1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43
  44
  45class _Expression(type):
  46    def __new__(cls, clsname, bases, attrs):
  47        klass = super().__new__(cls, clsname, bases, attrs)
  48
  49        # When an Expression class is created, its key is automatically set to be
  50        # the lowercase version of the class' name.
  51        klass.key = clsname.lower()
  52
  53        # This is so that docstrings are not inherited in pdoc
  54        klass.__doc__ = klass.__doc__ or ""
  55
  56        return klass
  57
  58
  59SQLGLOT_META = "sqlglot.meta"
  60TABLE_PARTS = ("this", "db", "catalog")
  61
  62
  63class Expression(metaclass=_Expression):
  64    """
  65    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  66    context, such as its child expressions, their names (arg keys), and whether a given child expression
  67    is optional or not.
  68
  69    Attributes:
  70        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  71            and representing expressions as strings.
  72        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  73            arg keys to booleans that indicate whether the corresponding args are optional.
  74        parent: a reference to the parent expression (or None, in case of root expressions).
  75        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  76            uses to refer to it.
  77        index: the index of an expression if it is inside of a list argument in its parent
  78        comments: a list of comments that are associated with a given expression. This is used in
  79            order to preserve comments when transpiling SQL code.
  80        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  81            optimizer, in order to enable some transformations that require type information.
  82        meta: a dictionary that can be used to store useful metadata for a given expression.
  83
  84    Example:
  85        >>> class Foo(Expression):
  86        ...     arg_types = {"this": True, "expression": False}
  87
  88        The above definition informs us that Foo is an Expression that requires an argument called
  89        "this" and may also optionally receive an argument called "expression".
  90
  91    Args:
  92        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  93    """
  94
  95    key = "expression"
  96    arg_types = {"this": True}
  97    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
  98
  99    def __init__(self, **args: t.Any):
 100        self.args: t.Dict[str, t.Any] = args
 101        self.parent: t.Optional[Expression] = None
 102        self.arg_key: t.Optional[str] = None
 103        self.index: t.Optional[int] = None
 104        self.comments: t.Optional[t.List[str]] = None
 105        self._type: t.Optional[DataType] = None
 106        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 107        self._hash: t.Optional[int] = None
 108
 109        for arg_key, value in self.args.items():
 110            self._set_parent(arg_key, value)
 111
 112    def __eq__(self, other) -> bool:
 113        return type(self) is type(other) and hash(self) == hash(other)
 114
 115    @property
 116    def hashable_args(self) -> t.Any:
 117        return frozenset(
 118            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 119            for k, v in self.args.items()
 120            if not (v is None or v is False or (type(v) is list and not v))
 121        )
 122
 123    def __hash__(self) -> int:
 124        if self._hash is not None:
 125            return self._hash
 126
 127        return hash((self.__class__, self.hashable_args))
 128
 129    @property
 130    def this(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "this".
 133        """
 134        return self.args.get("this")
 135
 136    @property
 137    def expression(self) -> t.Any:
 138        """
 139        Retrieves the argument with key "expression".
 140        """
 141        return self.args.get("expression")
 142
 143    @property
 144    def expressions(self) -> t.List[t.Any]:
 145        """
 146        Retrieves the argument with key "expressions".
 147        """
 148        return self.args.get("expressions") or []
 149
 150    def text(self, key) -> str:
 151        """
 152        Returns a textual representation of the argument corresponding to "key". This can only be used
 153        for args that are strings or leaf Expression instances, such as identifiers and literals.
 154        """
 155        field = self.args.get(key)
 156        if isinstance(field, str):
 157            return field
 158        if isinstance(field, (Identifier, Literal, Var)):
 159            return field.this
 160        if isinstance(field, (Star, Null)):
 161            return field.name
 162        return ""
 163
 164    @property
 165    def is_string(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a string.
 168        """
 169        return isinstance(self, Literal) and self.args["is_string"]
 170
 171    @property
 172    def is_number(self) -> bool:
 173        """
 174        Checks whether a Literal expression is a number.
 175        """
 176        return isinstance(self, Literal) and not self.args["is_string"]
 177
 178    @property
 179    def is_int(self) -> bool:
 180        """
 181        Checks whether a Literal expression is an integer.
 182        """
 183        return self.is_number and is_int(self.name)
 184
 185    @property
 186    def is_star(self) -> bool:
 187        """Checks whether an expression is a star."""
 188        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 189
 190    @property
 191    def alias(self) -> str:
 192        """
 193        Returns the alias of the expression, or an empty string if it's not aliased.
 194        """
 195        if isinstance(self.args.get("alias"), TableAlias):
 196            return self.args["alias"].name
 197        return self.text("alias")
 198
 199    @property
 200    def alias_column_names(self) -> t.List[str]:
 201        table_alias = self.args.get("alias")
 202        if not table_alias:
 203            return []
 204        return [c.name for c in table_alias.args.get("columns") or []]
 205
 206    @property
 207    def name(self) -> str:
 208        return self.text("this")
 209
 210    @property
 211    def alias_or_name(self) -> str:
 212        return self.alias or self.name
 213
 214    @property
 215    def output_name(self) -> str:
 216        """
 217        Name of the output column if this expression is a selection.
 218
 219        If the Expression has no output name, an empty string is returned.
 220
 221        Example:
 222            >>> from sqlglot import parse_one
 223            >>> parse_one("SELECT a").expressions[0].output_name
 224            'a'
 225            >>> parse_one("SELECT b AS c").expressions[0].output_name
 226            'c'
 227            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 228            ''
 229        """
 230        return ""
 231
 232    @property
 233    def type(self) -> t.Optional[DataType]:
 234        return self._type
 235
 236    @type.setter
 237    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 238        if dtype and not isinstance(dtype, DataType):
 239            dtype = DataType.build(dtype)
 240        self._type = dtype  # type: ignore
 241
 242    def is_type(self, *dtypes) -> bool:
 243        return self.type is not None and self.type.is_type(*dtypes)
 244
 245    def is_leaf(self) -> bool:
 246        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 247
 248    @property
 249    def meta(self) -> t.Dict[str, t.Any]:
 250        if self._meta is None:
 251            self._meta = {}
 252        return self._meta
 253
 254    def __deepcopy__(self, memo):
 255        root = self.__class__()
 256        stack = [(self, root)]
 257
 258        while stack:
 259            node, copy = stack.pop()
 260
 261            if node.comments is not None:
 262                copy.comments = deepcopy(node.comments)
 263            if node._type is not None:
 264                copy._type = deepcopy(node._type)
 265            if node._meta is not None:
 266                copy._meta = deepcopy(node._meta)
 267            if node._hash is not None:
 268                copy._hash = node._hash
 269
 270            for k, vs in node.args.items():
 271                if hasattr(vs, "parent"):
 272                    stack.append((vs, vs.__class__()))
 273                    copy.set(k, stack[-1][-1])
 274                elif type(vs) is list:
 275                    copy.args[k] = []
 276
 277                    for v in vs:
 278                        if hasattr(v, "parent"):
 279                            stack.append((v, v.__class__()))
 280                            copy.append(k, stack[-1][-1])
 281                        else:
 282                            copy.append(k, v)
 283                else:
 284                    copy.args[k] = vs
 285
 286        return root
 287
 288    def copy(self):
 289        """
 290        Returns a deep copy of the expression.
 291        """
 292        return deepcopy(self)
 293
 294    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 295        if self.comments is None:
 296            self.comments = []
 297        if comments:
 298            for comment in comments:
 299                _, *meta = comment.split(SQLGLOT_META)
 300                if meta:
 301                    for kv in "".join(meta).split(","):
 302                        k, *v = kv.split("=")
 303                        value = v[0].strip() if v else True
 304                        self.meta[k.strip()] = value
 305                self.comments.append(comment)
 306
 307    def append(self, arg_key: str, value: t.Any) -> None:
 308        """
 309        Appends value to arg_key if it's a list or sets it as a new list.
 310
 311        Args:
 312            arg_key (str): name of the list expression arg
 313            value (Any): value to append to the list
 314        """
 315        if type(self.args.get(arg_key)) is not list:
 316            self.args[arg_key] = []
 317        self._set_parent(arg_key, value)
 318        values = self.args[arg_key]
 319        if hasattr(value, "parent"):
 320            value.index = len(values)
 321        values.append(value)
 322
 323    def set(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Sets arg_key to value.
 326
 327        Args:
 328            arg_key: name of the expression arg.
 329            value: value to set the arg to.
 330        """
 331        if value is None:
 332            self.args.pop(arg_key, None)
 333        else:
 334            self.args[arg_key] = value
 335            self._set_parent(arg_key, value)
 336
 337    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 338        if hasattr(value, "parent"):
 339            value.parent = self
 340            value.arg_key = arg_key
 341            value.index = index
 342        elif type(value) is list:
 343            for index, v in enumerate(value):
 344                if hasattr(v, "parent"):
 345                    v.parent = self
 346                    v.arg_key = arg_key
 347                    v.index = index
 348
 349    @property
 350    def depth(self) -> int:
 351        """
 352        Returns the depth of this tree.
 353        """
 354        if self.parent:
 355            return self.parent.depth + 1
 356        return 0
 357
 358    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 359        """Yields the key and expression for all arguments, exploding list args."""
 360        # remove tuple when python 3.7 is deprecated
 361        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 362            if type(vs) is list:
 363                for v in reversed(vs) if reverse else vs:
 364                    if hasattr(v, "parent"):
 365                        yield v
 366            else:
 367                if hasattr(vs, "parent"):
 368                    yield vs
 369
 370    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 371        """
 372        Returns the first node in this tree which matches at least one of
 373        the specified types.
 374
 375        Args:
 376            expression_types: the expression type(s) to match.
 377            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 378
 379        Returns:
 380            The node which matches the criteria or None if no such node was found.
 381        """
 382        return next(self.find_all(*expression_types, bfs=bfs), None)
 383
 384    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 385        """
 386        Returns a generator object which visits all nodes in this tree and only
 387        yields those that match at least one of the specified expression types.
 388
 389        Args:
 390            expression_types: the expression type(s) to match.
 391            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 392
 393        Returns:
 394            The generator object.
 395        """
 396        for expression in self.walk(bfs=bfs):
 397            if isinstance(expression, expression_types):
 398                yield expression
 399
 400    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 401        """
 402        Returns a nearest parent matching expression_types.
 403
 404        Args:
 405            expression_types: the expression type(s) to match.
 406
 407        Returns:
 408            The parent node.
 409        """
 410        ancestor = self.parent
 411        while ancestor and not isinstance(ancestor, expression_types):
 412            ancestor = ancestor.parent
 413        return ancestor  # type: ignore
 414
 415    @property
 416    def parent_select(self) -> t.Optional[Select]:
 417        """
 418        Returns the parent select statement.
 419        """
 420        return self.find_ancestor(Select)
 421
 422    @property
 423    def same_parent(self) -> bool:
 424        """Returns if the parent is the same class as itself."""
 425        return type(self.parent) is self.__class__
 426
 427    def root(self) -> Expression:
 428        """
 429        Returns the root expression of this tree.
 430        """
 431        expression = self
 432        while expression.parent:
 433            expression = expression.parent
 434        return expression
 435
 436    def walk(
 437        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 438    ) -> t.Iterator[Expression]:
 439        """
 440        Returns a generator object which visits all nodes in this tree.
 441
 442        Args:
 443            bfs (bool): if set to True the BFS traversal order will be applied,
 444                otherwise the DFS traversal will be used instead.
 445            prune ((node, parent, arg_key) -> bool): callable that returns True if
 446                the generator should stop traversing this branch of the tree.
 447
 448        Returns:
 449            the generator object.
 450        """
 451        if bfs:
 452            yield from self.bfs(prune=prune)
 453        else:
 454            yield from self.dfs(prune=prune)
 455
 456    def dfs(
 457        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 458    ) -> t.Iterator[Expression]:
 459        """
 460        Returns a generator object which visits all nodes in this tree in
 461        the DFS (Depth-first) order.
 462
 463        Returns:
 464            The generator object.
 465        """
 466        stack = [self]
 467
 468        while stack:
 469            node = stack.pop()
 470
 471            yield node
 472
 473            if prune and prune(node):
 474                continue
 475
 476            for v in node.iter_expressions(reverse=True):
 477                stack.append(v)
 478
 479    def bfs(
 480        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 481    ) -> t.Iterator[Expression]:
 482        """
 483        Returns a generator object which visits all nodes in this tree in
 484        the BFS (Breadth-first) order.
 485
 486        Returns:
 487            The generator object.
 488        """
 489        queue = deque([self])
 490
 491        while queue:
 492            node = queue.popleft()
 493
 494            yield node
 495
 496            if prune and prune(node):
 497                continue
 498
 499            for v in node.iter_expressions():
 500                queue.append(v)
 501
 502    def unnest(self):
 503        """
 504        Returns the first non parenthesis child or self.
 505        """
 506        expression = self
 507        while type(expression) is Paren:
 508            expression = expression.this
 509        return expression
 510
 511    def unalias(self):
 512        """
 513        Returns the inner expression if this is an Alias.
 514        """
 515        if isinstance(self, Alias):
 516            return self.this
 517        return self
 518
 519    def unnest_operands(self):
 520        """
 521        Returns unnested operands as a tuple.
 522        """
 523        return tuple(arg.unnest() for arg in self.iter_expressions())
 524
 525    def flatten(self, unnest=True):
 526        """
 527        Returns a generator which yields child nodes whose parents are the same class.
 528
 529        A AND B AND C -> [A, B, C]
 530        """
 531        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 532            if type(node) is not self.__class__:
 533                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 534
 535    def __str__(self) -> str:
 536        return self.sql()
 537
 538    def __repr__(self) -> str:
 539        return _to_s(self)
 540
 541    def to_s(self) -> str:
 542        """
 543        Same as __repr__, but includes additional information which can be useful
 544        for debugging, like empty or missing args and the AST nodes' object IDs.
 545        """
 546        return _to_s(self, verbose=True)
 547
 548    def sql(self, dialect: DialectType = None, **opts) -> str:
 549        """
 550        Returns SQL string representation of this tree.
 551
 552        Args:
 553            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 554            opts: other `sqlglot.generator.Generator` options.
 555
 556        Returns:
 557            The SQL string.
 558        """
 559        from sqlglot.dialects import Dialect
 560
 561        return Dialect.get_or_raise(dialect).generate(self, **opts)
 562
 563    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 564        """
 565        Visits all tree nodes (excluding already transformed ones)
 566        and applies the given transformation function to each node.
 567
 568        Args:
 569            fun (function): a function which takes a node as an argument and returns a
 570                new transformed node or the same node without modifications. If the function
 571                returns None, then the corresponding node will be removed from the syntax tree.
 572            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 573                modified in place.
 574
 575        Returns:
 576            The transformed tree.
 577        """
 578        root = None
 579        new_node = None
 580
 581        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 582            new_node = fun(node, *args, **kwargs)
 583
 584            if root:
 585                if new_node is not node:
 586                    node.replace(new_node)
 587            else:
 588                root = new_node
 589
 590        assert root
 591        return root.assert_is(Expression)
 592
 593    @t.overload
 594    def replace(self, expression: E) -> E: ...
 595
 596    @t.overload
 597    def replace(self, expression: None) -> None: ...
 598
 599    def replace(self, expression):
 600        """
 601        Swap out this expression with a new expression.
 602
 603        For example::
 604
 605            >>> tree = Select().select("x").from_("tbl")
 606            >>> tree.find(Column).replace(column("y"))
 607            Column(
 608              this=Identifier(this=y, quoted=False))
 609            >>> tree.sql()
 610            'SELECT y FROM tbl'
 611
 612        Args:
 613            expression: new node
 614
 615        Returns:
 616            The new expression or expressions.
 617        """
 618        parent = self.parent
 619
 620        if not parent:
 621            return expression
 622
 623        key = self.arg_key
 624        value = parent.args.get(key)
 625        value_is_list = type(value) is list
 626        exp_is_list = type(expression) is list
 627
 628        if value_is_list:
 629            index = self.index
 630
 631            if exp_is_list:
 632                value.pop(index)
 633                value[index:index] = expression
 634                parent._set_parent(key, value)
 635            else:
 636                if expression is None:
 637                    value.pop(index)
 638
 639                    for v in value[index:]:
 640                        v.index = v.index - 1
 641                else:
 642                    value[index] = expression
 643                    parent._set_parent(key, expression, index=index)
 644        elif value is not None:
 645            if expression is None:
 646                parent.args.pop(key)
 647            else:
 648                if exp_is_list and not value_is_list and value.parent:
 649                    value.parent.replace(expression)
 650                else:
 651                    parent.set(key, expression)
 652
 653        if expression is not self:
 654            self.parent = None
 655            self.arg_key = None
 656            self.index = None
 657
 658        return expression
 659
 660    def pop(self: E) -> E:
 661        """
 662        Remove this expression from its AST.
 663
 664        Returns:
 665            The popped expression.
 666        """
 667        self.replace(None)
 668        return self
 669
 670    def assert_is(self, type_: t.Type[E]) -> E:
 671        """
 672        Assert that this `Expression` is an instance of `type_`.
 673
 674        If it is NOT an instance of `type_`, this raises an assertion error.
 675        Otherwise, this returns this expression.
 676
 677        Examples:
 678            This is useful for type security in chained expressions:
 679
 680            >>> import sqlglot
 681            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 682            'SELECT x, z FROM y'
 683        """
 684        if not isinstance(self, type_):
 685            raise AssertionError(f"{self} is not {type_}.")
 686        return self
 687
 688    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 689        """
 690        Checks if this expression is valid (e.g. all mandatory args are set).
 691
 692        Args:
 693            args: a sequence of values that were used to instantiate a Func expression. This is used
 694                to check that the provided arguments don't exceed the function argument limit.
 695
 696        Returns:
 697            A list of error messages for all possible errors that were found.
 698        """
 699        errors: t.List[str] = []
 700
 701        for k in self.args:
 702            if k not in self.arg_types:
 703                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 704        for k, mandatory in self.arg_types.items():
 705            v = self.args.get(k)
 706            if mandatory and (v is None or (isinstance(v, list) and not v)):
 707                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 708
 709        if (
 710            args
 711            and isinstance(self, Func)
 712            and len(args) > len(self.arg_types)
 713            and not self.is_var_len_args
 714        ):
 715            errors.append(
 716                f"The number of provided arguments ({len(args)}) is greater than "
 717                f"the maximum number of supported arguments ({len(self.arg_types)})"
 718            )
 719
 720        return errors
 721
 722    def dump(self):
 723        """
 724        Dump this Expression to a JSON-serializable dict.
 725        """
 726        from sqlglot.serde import dump
 727
 728        return dump(self)
 729
 730    @classmethod
 731    def load(cls, obj):
 732        """
 733        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 734        """
 735        from sqlglot.serde import load
 736
 737        return load(obj)
 738
 739    def and_(
 740        self,
 741        *expressions: t.Optional[ExpOrStr],
 742        dialect: DialectType = None,
 743        copy: bool = True,
 744        **opts,
 745    ) -> Condition:
 746        """
 747        AND this condition with one or multiple expressions.
 748
 749        Example:
 750            >>> condition("x=1").and_("y=1").sql()
 751            'x = 1 AND y = 1'
 752
 753        Args:
 754            *expressions: the SQL code strings to parse.
 755                If an `Expression` instance is passed, it will be used as-is.
 756            dialect: the dialect used to parse the input expression.
 757            copy: whether to copy the involved expressions (only applies to Expressions).
 758            opts: other options to use to parse the input expressions.
 759
 760        Returns:
 761            The new And condition.
 762        """
 763        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 764
 765    def or_(
 766        self,
 767        *expressions: t.Optional[ExpOrStr],
 768        dialect: DialectType = None,
 769        copy: bool = True,
 770        **opts,
 771    ) -> Condition:
 772        """
 773        OR this condition with one or multiple expressions.
 774
 775        Example:
 776            >>> condition("x=1").or_("y=1").sql()
 777            'x = 1 OR y = 1'
 778
 779        Args:
 780            *expressions: the SQL code strings to parse.
 781                If an `Expression` instance is passed, it will be used as-is.
 782            dialect: the dialect used to parse the input expression.
 783            copy: whether to copy the involved expressions (only applies to Expressions).
 784            opts: other options to use to parse the input expressions.
 785
 786        Returns:
 787            The new Or condition.
 788        """
 789        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 790
 791    def not_(self, copy: bool = True):
 792        """
 793        Wrap this condition with NOT.
 794
 795        Example:
 796            >>> condition("x=1").not_().sql()
 797            'NOT x = 1'
 798
 799        Args:
 800            copy: whether to copy this object.
 801
 802        Returns:
 803            The new Not instance.
 804        """
 805        return not_(self, copy=copy)
 806
 807    def as_(
 808        self,
 809        alias: str | Identifier,
 810        quoted: t.Optional[bool] = None,
 811        dialect: DialectType = None,
 812        copy: bool = True,
 813        **opts,
 814    ) -> Alias:
 815        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 816
 817    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 818        this = self.copy()
 819        other = convert(other, copy=True)
 820        if not isinstance(this, klass) and not isinstance(other, klass):
 821            this = _wrap(this, Binary)
 822            other = _wrap(other, Binary)
 823        if reverse:
 824            return klass(this=other, expression=this)
 825        return klass(this=this, expression=other)
 826
 827    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 828        return Bracket(
 829            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 830        )
 831
 832    def __iter__(self) -> t.Iterator:
 833        if "expressions" in self.arg_types:
 834            return iter(self.args.get("expressions") or [])
 835        # We define this because __getitem__ converts Expression into an iterable, which is
 836        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 837        # See: https://peps.python.org/pep-0234/
 838        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 839
 840    def isin(
 841        self,
 842        *expressions: t.Any,
 843        query: t.Optional[ExpOrStr] = None,
 844        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 845        copy: bool = True,
 846        **opts,
 847    ) -> In:
 848        return In(
 849            this=maybe_copy(self, copy),
 850            expressions=[convert(e, copy=copy) for e in expressions],
 851            query=maybe_parse(query, copy=copy, **opts) if query else None,
 852            unnest=(
 853                Unnest(
 854                    expressions=[
 855                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 856                        for e in ensure_list(unnest)
 857                    ]
 858                )
 859                if unnest
 860                else None
 861            ),
 862        )
 863
 864    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 865        return Between(
 866            this=maybe_copy(self, copy),
 867            low=convert(low, copy=copy, **opts),
 868            high=convert(high, copy=copy, **opts),
 869        )
 870
 871    def is_(self, other: ExpOrStr) -> Is:
 872        return self._binop(Is, other)
 873
 874    def like(self, other: ExpOrStr) -> Like:
 875        return self._binop(Like, other)
 876
 877    def ilike(self, other: ExpOrStr) -> ILike:
 878        return self._binop(ILike, other)
 879
 880    def eq(self, other: t.Any) -> EQ:
 881        return self._binop(EQ, other)
 882
 883    def neq(self, other: t.Any) -> NEQ:
 884        return self._binop(NEQ, other)
 885
 886    def rlike(self, other: ExpOrStr) -> RegexpLike:
 887        return self._binop(RegexpLike, other)
 888
 889    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 890        div = self._binop(Div, other)
 891        div.args["typed"] = typed
 892        div.args["safe"] = safe
 893        return div
 894
 895    def asc(self, nulls_first: bool = True) -> Ordered:
 896        return Ordered(this=self.copy(), nulls_first=nulls_first)
 897
 898    def desc(self, nulls_first: bool = False) -> Ordered:
 899        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 900
 901    def __lt__(self, other: t.Any) -> LT:
 902        return self._binop(LT, other)
 903
 904    def __le__(self, other: t.Any) -> LTE:
 905        return self._binop(LTE, other)
 906
 907    def __gt__(self, other: t.Any) -> GT:
 908        return self._binop(GT, other)
 909
 910    def __ge__(self, other: t.Any) -> GTE:
 911        return self._binop(GTE, other)
 912
 913    def __add__(self, other: t.Any) -> Add:
 914        return self._binop(Add, other)
 915
 916    def __radd__(self, other: t.Any) -> Add:
 917        return self._binop(Add, other, reverse=True)
 918
 919    def __sub__(self, other: t.Any) -> Sub:
 920        return self._binop(Sub, other)
 921
 922    def __rsub__(self, other: t.Any) -> Sub:
 923        return self._binop(Sub, other, reverse=True)
 924
 925    def __mul__(self, other: t.Any) -> Mul:
 926        return self._binop(Mul, other)
 927
 928    def __rmul__(self, other: t.Any) -> Mul:
 929        return self._binop(Mul, other, reverse=True)
 930
 931    def __truediv__(self, other: t.Any) -> Div:
 932        return self._binop(Div, other)
 933
 934    def __rtruediv__(self, other: t.Any) -> Div:
 935        return self._binop(Div, other, reverse=True)
 936
 937    def __floordiv__(self, other: t.Any) -> IntDiv:
 938        return self._binop(IntDiv, other)
 939
 940    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 941        return self._binop(IntDiv, other, reverse=True)
 942
 943    def __mod__(self, other: t.Any) -> Mod:
 944        return self._binop(Mod, other)
 945
 946    def __rmod__(self, other: t.Any) -> Mod:
 947        return self._binop(Mod, other, reverse=True)
 948
 949    def __pow__(self, other: t.Any) -> Pow:
 950        return self._binop(Pow, other)
 951
 952    def __rpow__(self, other: t.Any) -> Pow:
 953        return self._binop(Pow, other, reverse=True)
 954
 955    def __and__(self, other: t.Any) -> And:
 956        return self._binop(And, other)
 957
 958    def __rand__(self, other: t.Any) -> And:
 959        return self._binop(And, other, reverse=True)
 960
 961    def __or__(self, other: t.Any) -> Or:
 962        return self._binop(Or, other)
 963
 964    def __ror__(self, other: t.Any) -> Or:
 965        return self._binop(Or, other, reverse=True)
 966
 967    def __neg__(self) -> Neg:
 968        return Neg(this=_wrap(self.copy(), Binary))
 969
 970    def __invert__(self) -> Not:
 971        return not_(self.copy())
 972
 973
 974IntoType = t.Union[
 975    str,
 976    t.Type[Expression],
 977    t.Collection[t.Union[str, t.Type[Expression]]],
 978]
 979ExpOrStr = t.Union[str, Expression]
 980
 981
 982class Condition(Expression):
 983    """Logical conditions like x AND y, or simply x"""
 984
 985
 986class Predicate(Condition):
 987    """Relationships like x = y, x > 1, x >= y."""
 988
 989
 990class DerivedTable(Expression):
 991    @property
 992    def selects(self) -> t.List[Expression]:
 993        return self.this.selects if isinstance(self.this, Query) else []
 994
 995    @property
 996    def named_selects(self) -> t.List[str]:
 997        return [select.output_name for select in self.selects]
 998
 999
1000class Query(Expression):
1001    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1002        """
1003        Returns a `Subquery` that wraps around this query.
1004
1005        Example:
1006            >>> subquery = Select().select("x").from_("tbl").subquery()
1007            >>> Select().select("x").from_(subquery).sql()
1008            'SELECT x FROM (SELECT x FROM tbl)'
1009
1010        Args:
1011            alias: an optional alias for the subquery.
1012            copy: if `False`, modify this expression instance in-place.
1013        """
1014        instance = maybe_copy(self, copy)
1015        if not isinstance(alias, Expression):
1016            alias = TableAlias(this=to_identifier(alias)) if alias else None
1017
1018        return Subquery(this=instance, alias=alias)
1019
1020    def limit(
1021        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1022    ) -> Select:
1023        """
1024        Adds a LIMIT clause to this query.
1025
1026        Example:
1027            >>> select("1").union(select("1")).limit(1).sql()
1028            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1029
1030        Args:
1031            expression: the SQL code string to parse.
1032                This can also be an integer.
1033                If a `Limit` instance is passed, it will be used as-is.
1034                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1035            dialect: the dialect used to parse the input expression.
1036            copy: if `False`, modify this expression instance in-place.
1037            opts: other options to use to parse the input expressions.
1038
1039        Returns:
1040            A limited Select expression.
1041        """
1042        return (
1043            select("*")
1044            .from_(self.subquery(alias="_l_0", copy=copy))
1045            .limit(expression, dialect=dialect, copy=False, **opts)
1046        )
1047
1048    @property
1049    def ctes(self) -> t.List[CTE]:
1050        """Returns a list of all the CTEs attached to this query."""
1051        with_ = self.args.get("with")
1052        return with_.expressions if with_ else []
1053
1054    @property
1055    def selects(self) -> t.List[Expression]:
1056        """Returns the query's projections."""
1057        raise NotImplementedError("Query objects must implement `selects`")
1058
1059    @property
1060    def named_selects(self) -> t.List[str]:
1061        """Returns the output names of the query's projections."""
1062        raise NotImplementedError("Query objects must implement `named_selects`")
1063
1064    def select(
1065        self: Q,
1066        *expressions: t.Optional[ExpOrStr],
1067        append: bool = True,
1068        dialect: DialectType = None,
1069        copy: bool = True,
1070        **opts,
1071    ) -> Q:
1072        """
1073        Append to or set the SELECT expressions.
1074
1075        Example:
1076            >>> Select().select("x", "y").sql()
1077            'SELECT x, y'
1078
1079        Args:
1080            *expressions: the SQL code strings to parse.
1081                If an `Expression` instance is passed, it will be used as-is.
1082            append: if `True`, add to any existing expressions.
1083                Otherwise, this resets the expressions.
1084            dialect: the dialect used to parse the input expressions.
1085            copy: if `False`, modify this expression instance in-place.
1086            opts: other options to use to parse the input expressions.
1087
1088        Returns:
1089            The modified Query expression.
1090        """
1091        raise NotImplementedError("Query objects must implement `select`")
1092
1093    def with_(
1094        self: Q,
1095        alias: ExpOrStr,
1096        as_: ExpOrStr,
1097        recursive: t.Optional[bool] = None,
1098        append: bool = True,
1099        dialect: DialectType = None,
1100        copy: bool = True,
1101        **opts,
1102    ) -> Q:
1103        """
1104        Append to or set the common table expressions.
1105
1106        Example:
1107            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1108            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1109
1110        Args:
1111            alias: the SQL code string to parse as the table name.
1112                If an `Expression` instance is passed, this is used as-is.
1113            as_: the SQL code string to parse as the table expression.
1114                If an `Expression` instance is passed, it will be used as-is.
1115            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1116            append: if `True`, add to any existing expressions.
1117                Otherwise, this resets the expressions.
1118            dialect: the dialect used to parse the input expression.
1119            copy: if `False`, modify this expression instance in-place.
1120            opts: other options to use to parse the input expressions.
1121
1122        Returns:
1123            The modified expression.
1124        """
1125        return _apply_cte_builder(
1126            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1127        )
1128
1129    def union(
1130        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1131    ) -> Union:
1132        """
1133        Builds a UNION expression.
1134
1135        Example:
1136            >>> import sqlglot
1137            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1138            'SELECT * FROM foo UNION SELECT * FROM bla'
1139
1140        Args:
1141            expression: the SQL code string.
1142                If an `Expression` instance is passed, it will be used as-is.
1143            distinct: set the DISTINCT flag if and only if this is true.
1144            dialect: the dialect used to parse the input expression.
1145            opts: other options to use to parse the input expressions.
1146
1147        Returns:
1148            The new Union expression.
1149        """
1150        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1151
1152    def intersect(
1153        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1154    ) -> Intersect:
1155        """
1156        Builds an INTERSECT expression.
1157
1158        Example:
1159            >>> import sqlglot
1160            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1161            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1162
1163        Args:
1164            expression: the SQL code string.
1165                If an `Expression` instance is passed, it will be used as-is.
1166            distinct: set the DISTINCT flag if and only if this is true.
1167            dialect: the dialect used to parse the input expression.
1168            opts: other options to use to parse the input expressions.
1169
1170        Returns:
1171            The new Intersect expression.
1172        """
1173        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1174
1175    def except_(
1176        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1177    ) -> Except:
1178        """
1179        Builds an EXCEPT expression.
1180
1181        Example:
1182            >>> import sqlglot
1183            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1184            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1185
1186        Args:
1187            expression: the SQL code string.
1188                If an `Expression` instance is passed, it will be used as-is.
1189            distinct: set the DISTINCT flag if and only if this is true.
1190            dialect: the dialect used to parse the input expression.
1191            opts: other options to use to parse the input expressions.
1192
1193        Returns:
1194            The new Except expression.
1195        """
1196        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1197
1198
1199class UDTF(DerivedTable):
1200    @property
1201    def selects(self) -> t.List[Expression]:
1202        alias = self.args.get("alias")
1203        return alias.columns if alias else []
1204
1205
1206class Cache(Expression):
1207    arg_types = {
1208        "this": True,
1209        "lazy": False,
1210        "options": False,
1211        "expression": False,
1212    }
1213
1214
1215class Uncache(Expression):
1216    arg_types = {"this": True, "exists": False}
1217
1218
1219class Refresh(Expression):
1220    pass
1221
1222
1223class DDL(Expression):
1224    @property
1225    def ctes(self) -> t.List[CTE]:
1226        """Returns a list of all the CTEs attached to this statement."""
1227        with_ = self.args.get("with")
1228        return with_.expressions if with_ else []
1229
1230    @property
1231    def selects(self) -> t.List[Expression]:
1232        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1233        return self.expression.selects if isinstance(self.expression, Query) else []
1234
1235    @property
1236    def named_selects(self) -> t.List[str]:
1237        """
1238        If this statement contains a query (e.g. a CTAS), this returns the output
1239        names of the query's projections.
1240        """
1241        return self.expression.named_selects if isinstance(self.expression, Query) else []
1242
1243
1244class DML(Expression):
1245    def returning(
1246        self,
1247        expression: ExpOrStr,
1248        dialect: DialectType = None,
1249        copy: bool = True,
1250        **opts,
1251    ) -> DML:
1252        """
1253        Set the RETURNING expression. Not supported by all dialects.
1254
1255        Example:
1256            >>> delete("tbl").returning("*", dialect="postgres").sql()
1257            'DELETE FROM tbl RETURNING *'
1258
1259        Args:
1260            expression: the SQL code strings to parse.
1261                If an `Expression` instance is passed, it will be used as-is.
1262            dialect: the dialect used to parse the input expressions.
1263            copy: if `False`, modify this expression instance in-place.
1264            opts: other options to use to parse the input expressions.
1265
1266        Returns:
1267            Delete: the modified expression.
1268        """
1269        return _apply_builder(
1270            expression=expression,
1271            instance=self,
1272            arg="returning",
1273            prefix="RETURNING",
1274            dialect=dialect,
1275            copy=copy,
1276            into=Returning,
1277            **opts,
1278        )
1279
1280
1281class Create(DDL):
1282    arg_types = {
1283        "with": False,
1284        "this": True,
1285        "kind": True,
1286        "expression": False,
1287        "exists": False,
1288        "properties": False,
1289        "replace": False,
1290        "unique": False,
1291        "indexes": False,
1292        "no_schema_binding": False,
1293        "begin": False,
1294        "end": False,
1295        "clone": False,
1296    }
1297
1298    @property
1299    def kind(self) -> t.Optional[str]:
1300        kind = self.args.get("kind")
1301        return kind and kind.upper()
1302
1303
1304class SequenceProperties(Expression):
1305    arg_types = {
1306        "increment": False,
1307        "minvalue": False,
1308        "maxvalue": False,
1309        "cache": False,
1310        "start": False,
1311        "owned": False,
1312        "options": False,
1313    }
1314
1315
1316class TruncateTable(Expression):
1317    arg_types = {
1318        "expressions": True,
1319        "is_database": False,
1320        "exists": False,
1321        "only": False,
1322        "cluster": False,
1323        "identity": False,
1324        "option": False,
1325        "partition": False,
1326    }
1327
1328
1329# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1330# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1331# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1332class Clone(Expression):
1333    arg_types = {"this": True, "shallow": False, "copy": False}
1334
1335
1336class Describe(Expression):
1337    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1338
1339
1340class Kill(Expression):
1341    arg_types = {"this": True, "kind": False}
1342
1343
1344class Pragma(Expression):
1345    pass
1346
1347
1348class Set(Expression):
1349    arg_types = {"expressions": False, "unset": False, "tag": False}
1350
1351
1352class Heredoc(Expression):
1353    arg_types = {"this": True, "tag": False}
1354
1355
1356class SetItem(Expression):
1357    arg_types = {
1358        "this": False,
1359        "expressions": False,
1360        "kind": False,
1361        "collate": False,  # MySQL SET NAMES statement
1362        "global": False,
1363    }
1364
1365
1366class Show(Expression):
1367    arg_types = {
1368        "this": True,
1369        "history": False,
1370        "terse": False,
1371        "target": False,
1372        "offset": False,
1373        "starts_with": False,
1374        "limit": False,
1375        "from": False,
1376        "like": False,
1377        "where": False,
1378        "db": False,
1379        "scope": False,
1380        "scope_kind": False,
1381        "full": False,
1382        "mutex": False,
1383        "query": False,
1384        "channel": False,
1385        "global": False,
1386        "log": False,
1387        "position": False,
1388        "types": False,
1389    }
1390
1391
1392class UserDefinedFunction(Expression):
1393    arg_types = {"this": True, "expressions": False, "wrapped": False}
1394
1395
1396class CharacterSet(Expression):
1397    arg_types = {"this": True, "default": False}
1398
1399
1400class With(Expression):
1401    arg_types = {"expressions": True, "recursive": False}
1402
1403    @property
1404    def recursive(self) -> bool:
1405        return bool(self.args.get("recursive"))
1406
1407
1408class WithinGroup(Expression):
1409    arg_types = {"this": True, "expression": False}
1410
1411
1412# clickhouse supports scalar ctes
1413# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1414class CTE(DerivedTable):
1415    arg_types = {
1416        "this": True,
1417        "alias": True,
1418        "scalar": False,
1419        "materialized": False,
1420    }
1421
1422
1423class TableAlias(Expression):
1424    arg_types = {"this": False, "columns": False}
1425
1426    @property
1427    def columns(self):
1428        return self.args.get("columns") or []
1429
1430
1431class BitString(Condition):
1432    pass
1433
1434
1435class HexString(Condition):
1436    pass
1437
1438
1439class ByteString(Condition):
1440    pass
1441
1442
1443class RawString(Condition):
1444    pass
1445
1446
1447class UnicodeString(Condition):
1448    arg_types = {"this": True, "escape": False}
1449
1450
1451class Column(Condition):
1452    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1453
1454    @property
1455    def table(self) -> str:
1456        return self.text("table")
1457
1458    @property
1459    def db(self) -> str:
1460        return self.text("db")
1461
1462    @property
1463    def catalog(self) -> str:
1464        return self.text("catalog")
1465
1466    @property
1467    def output_name(self) -> str:
1468        return self.name
1469
1470    @property
1471    def parts(self) -> t.List[Identifier]:
1472        """Return the parts of a column in order catalog, db, table, name."""
1473        return [
1474            t.cast(Identifier, self.args[part])
1475            for part in ("catalog", "db", "table", "this")
1476            if self.args.get(part)
1477        ]
1478
1479    def to_dot(self) -> Dot | Identifier:
1480        """Converts the column into a dot expression."""
1481        parts = self.parts
1482        parent = self.parent
1483
1484        while parent:
1485            if isinstance(parent, Dot):
1486                parts.append(parent.expression)
1487            parent = parent.parent
1488
1489        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1490
1491
1492class ColumnPosition(Expression):
1493    arg_types = {"this": False, "position": True}
1494
1495
1496class ColumnDef(Expression):
1497    arg_types = {
1498        "this": True,
1499        "kind": False,
1500        "constraints": False,
1501        "exists": False,
1502        "position": False,
1503    }
1504
1505    @property
1506    def constraints(self) -> t.List[ColumnConstraint]:
1507        return self.args.get("constraints") or []
1508
1509    @property
1510    def kind(self) -> t.Optional[DataType]:
1511        return self.args.get("kind")
1512
1513
1514class AlterColumn(Expression):
1515    arg_types = {
1516        "this": True,
1517        "dtype": False,
1518        "collate": False,
1519        "using": False,
1520        "default": False,
1521        "drop": False,
1522        "comment": False,
1523    }
1524
1525
1526class RenameColumn(Expression):
1527    arg_types = {"this": True, "to": True, "exists": False}
1528
1529
1530class RenameTable(Expression):
1531    pass
1532
1533
1534class SwapTable(Expression):
1535    pass
1536
1537
1538class Comment(Expression):
1539    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1540
1541
1542class Comprehension(Expression):
1543    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1544
1545
1546# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1547class MergeTreeTTLAction(Expression):
1548    arg_types = {
1549        "this": True,
1550        "delete": False,
1551        "recompress": False,
1552        "to_disk": False,
1553        "to_volume": False,
1554    }
1555
1556
1557# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1558class MergeTreeTTL(Expression):
1559    arg_types = {
1560        "expressions": True,
1561        "where": False,
1562        "group": False,
1563        "aggregates": False,
1564    }
1565
1566
1567# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1568class IndexConstraintOption(Expression):
1569    arg_types = {
1570        "key_block_size": False,
1571        "using": False,
1572        "parser": False,
1573        "comment": False,
1574        "visible": False,
1575        "engine_attr": False,
1576        "secondary_engine_attr": False,
1577    }
1578
1579
1580class ColumnConstraint(Expression):
1581    arg_types = {"this": False, "kind": True}
1582
1583    @property
1584    def kind(self) -> ColumnConstraintKind:
1585        return self.args["kind"]
1586
1587
1588class ColumnConstraintKind(Expression):
1589    pass
1590
1591
1592class AutoIncrementColumnConstraint(ColumnConstraintKind):
1593    pass
1594
1595
1596class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1597    arg_types = {"this": True, "expression": True}
1598
1599
1600class CaseSpecificColumnConstraint(ColumnConstraintKind):
1601    arg_types = {"not_": True}
1602
1603
1604class CharacterSetColumnConstraint(ColumnConstraintKind):
1605    arg_types = {"this": True}
1606
1607
1608class CheckColumnConstraint(ColumnConstraintKind):
1609    arg_types = {"this": True, "enforced": False}
1610
1611
1612class ClusteredColumnConstraint(ColumnConstraintKind):
1613    pass
1614
1615
1616class CollateColumnConstraint(ColumnConstraintKind):
1617    pass
1618
1619
1620class CommentColumnConstraint(ColumnConstraintKind):
1621    pass
1622
1623
1624class CompressColumnConstraint(ColumnConstraintKind):
1625    pass
1626
1627
1628class DateFormatColumnConstraint(ColumnConstraintKind):
1629    arg_types = {"this": True}
1630
1631
1632class DefaultColumnConstraint(ColumnConstraintKind):
1633    pass
1634
1635
1636class EncodeColumnConstraint(ColumnConstraintKind):
1637    pass
1638
1639
1640# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1641class ExcludeColumnConstraint(ColumnConstraintKind):
1642    pass
1643
1644
1645class WithOperator(Expression):
1646    arg_types = {"this": True, "op": True}
1647
1648
1649class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1650    # this: True -> ALWAYS, this: False -> BY DEFAULT
1651    arg_types = {
1652        "this": False,
1653        "expression": False,
1654        "on_null": False,
1655        "start": False,
1656        "increment": False,
1657        "minvalue": False,
1658        "maxvalue": False,
1659        "cycle": False,
1660    }
1661
1662
1663class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1664    arg_types = {"start": False, "hidden": False}
1665
1666
1667# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1668class IndexColumnConstraint(ColumnConstraintKind):
1669    arg_types = {
1670        "this": False,
1671        "schema": True,
1672        "kind": False,
1673        "index_type": False,
1674        "options": False,
1675    }
1676
1677
1678class InlineLengthColumnConstraint(ColumnConstraintKind):
1679    pass
1680
1681
1682class NonClusteredColumnConstraint(ColumnConstraintKind):
1683    pass
1684
1685
1686class NotForReplicationColumnConstraint(ColumnConstraintKind):
1687    arg_types = {}
1688
1689
1690class NotNullColumnConstraint(ColumnConstraintKind):
1691    arg_types = {"allow_null": False}
1692
1693
1694# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1695class OnUpdateColumnConstraint(ColumnConstraintKind):
1696    pass
1697
1698
1699# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1700class TransformColumnConstraint(ColumnConstraintKind):
1701    pass
1702
1703
1704class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1705    arg_types = {"desc": False}
1706
1707
1708class TitleColumnConstraint(ColumnConstraintKind):
1709    pass
1710
1711
1712class UniqueColumnConstraint(ColumnConstraintKind):
1713    arg_types = {"this": False, "index_type": False, "on_conflict": False}
1714
1715
1716class UppercaseColumnConstraint(ColumnConstraintKind):
1717    arg_types: t.Dict[str, t.Any] = {}
1718
1719
1720class PathColumnConstraint(ColumnConstraintKind):
1721    pass
1722
1723
1724# computed column expression
1725# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1726class ComputedColumnConstraint(ColumnConstraintKind):
1727    arg_types = {"this": True, "persisted": False, "not_null": False}
1728
1729
1730class Constraint(Expression):
1731    arg_types = {"this": True, "expressions": True}
1732
1733
1734class Delete(DML):
1735    arg_types = {
1736        "with": False,
1737        "this": False,
1738        "using": False,
1739        "where": False,
1740        "returning": False,
1741        "limit": False,
1742        "tables": False,  # Multiple-Table Syntax (MySQL)
1743    }
1744
1745    def delete(
1746        self,
1747        table: ExpOrStr,
1748        dialect: DialectType = None,
1749        copy: bool = True,
1750        **opts,
1751    ) -> Delete:
1752        """
1753        Create a DELETE expression or replace the table on an existing DELETE expression.
1754
1755        Example:
1756            >>> delete("tbl").sql()
1757            'DELETE FROM tbl'
1758
1759        Args:
1760            table: the table from which to delete.
1761            dialect: the dialect used to parse the input expression.
1762            copy: if `False`, modify this expression instance in-place.
1763            opts: other options to use to parse the input expressions.
1764
1765        Returns:
1766            Delete: the modified expression.
1767        """
1768        return _apply_builder(
1769            expression=table,
1770            instance=self,
1771            arg="this",
1772            dialect=dialect,
1773            into=Table,
1774            copy=copy,
1775            **opts,
1776        )
1777
1778    def where(
1779        self,
1780        *expressions: t.Optional[ExpOrStr],
1781        append: bool = True,
1782        dialect: DialectType = None,
1783        copy: bool = True,
1784        **opts,
1785    ) -> Delete:
1786        """
1787        Append to or set the WHERE expressions.
1788
1789        Example:
1790            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1791            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1792
1793        Args:
1794            *expressions: the SQL code strings to parse.
1795                If an `Expression` instance is passed, it will be used as-is.
1796                Multiple expressions are combined with an AND operator.
1797            append: if `True`, AND the new expressions to any existing expression.
1798                Otherwise, this resets the expression.
1799            dialect: the dialect used to parse the input expressions.
1800            copy: if `False`, modify this expression instance in-place.
1801            opts: other options to use to parse the input expressions.
1802
1803        Returns:
1804            Delete: the modified expression.
1805        """
1806        return _apply_conjunction_builder(
1807            *expressions,
1808            instance=self,
1809            arg="where",
1810            append=append,
1811            into=Where,
1812            dialect=dialect,
1813            copy=copy,
1814            **opts,
1815        )
1816
1817
1818class Drop(Expression):
1819    arg_types = {
1820        "this": False,
1821        "kind": False,
1822        "expressions": False,
1823        "exists": False,
1824        "temporary": False,
1825        "materialized": False,
1826        "cascade": False,
1827        "constraints": False,
1828        "purge": False,
1829    }
1830
1831
1832class Filter(Expression):
1833    arg_types = {"this": True, "expression": True}
1834
1835
1836class Check(Expression):
1837    pass
1838
1839
1840# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1841class Connect(Expression):
1842    arg_types = {"start": False, "connect": True}
1843
1844
1845class Prior(Expression):
1846    pass
1847
1848
1849class Directory(Expression):
1850    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1851    arg_types = {"this": True, "local": False, "row_format": False}
1852
1853
1854class ForeignKey(Expression):
1855    arg_types = {
1856        "expressions": True,
1857        "reference": False,
1858        "delete": False,
1859        "update": False,
1860    }
1861
1862
1863class ColumnPrefix(Expression):
1864    arg_types = {"this": True, "expression": True}
1865
1866
1867class PrimaryKey(Expression):
1868    arg_types = {"expressions": True, "options": False}
1869
1870
1871# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1872# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1873class Into(Expression):
1874    arg_types = {"this": True, "temporary": False, "unlogged": False}
1875
1876
1877class From(Expression):
1878    @property
1879    def name(self) -> str:
1880        return self.this.name
1881
1882    @property
1883    def alias_or_name(self) -> str:
1884        return self.this.alias_or_name
1885
1886
1887class Having(Expression):
1888    pass
1889
1890
1891class Hint(Expression):
1892    arg_types = {"expressions": True}
1893
1894
1895class JoinHint(Expression):
1896    arg_types = {"this": True, "expressions": True}
1897
1898
1899class Identifier(Expression):
1900    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1901
1902    @property
1903    def quoted(self) -> bool:
1904        return bool(self.args.get("quoted"))
1905
1906    @property
1907    def hashable_args(self) -> t.Any:
1908        return (self.this, self.quoted)
1909
1910    @property
1911    def output_name(self) -> str:
1912        return self.name
1913
1914
1915# https://www.postgresql.org/docs/current/indexes-opclass.html
1916class Opclass(Expression):
1917    arg_types = {"this": True, "expression": True}
1918
1919
1920class Index(Expression):
1921    arg_types = {
1922        "this": False,
1923        "table": False,
1924        "unique": False,
1925        "primary": False,
1926        "amp": False,  # teradata
1927        "params": False,
1928    }
1929
1930
1931class IndexParameters(Expression):
1932    arg_types = {
1933        "using": False,
1934        "include": False,
1935        "columns": False,
1936        "with_storage": False,
1937        "partition_by": False,
1938        "tablespace": False,
1939        "where": False,
1940    }
1941
1942
1943class Insert(DDL, DML):
1944    arg_types = {
1945        "hint": False,
1946        "with": False,
1947        "is_function": False,
1948        "this": True,
1949        "expression": False,
1950        "conflict": False,
1951        "returning": False,
1952        "overwrite": False,
1953        "exists": False,
1954        "partition": False,
1955        "alternative": False,
1956        "where": False,
1957        "ignore": False,
1958        "by_name": False,
1959    }
1960
1961    def with_(
1962        self,
1963        alias: ExpOrStr,
1964        as_: ExpOrStr,
1965        recursive: t.Optional[bool] = None,
1966        append: bool = True,
1967        dialect: DialectType = None,
1968        copy: bool = True,
1969        **opts,
1970    ) -> Insert:
1971        """
1972        Append to or set the common table expressions.
1973
1974        Example:
1975            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1976            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1977
1978        Args:
1979            alias: the SQL code string to parse as the table name.
1980                If an `Expression` instance is passed, this is used as-is.
1981            as_: the SQL code string to parse as the table expression.
1982                If an `Expression` instance is passed, it will be used as-is.
1983            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1984            append: if `True`, add to any existing expressions.
1985                Otherwise, this resets the expressions.
1986            dialect: the dialect used to parse the input expression.
1987            copy: if `False`, modify this expression instance in-place.
1988            opts: other options to use to parse the input expressions.
1989
1990        Returns:
1991            The modified expression.
1992        """
1993        return _apply_cte_builder(
1994            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1995        )
1996
1997
1998class OnConflict(Expression):
1999    arg_types = {
2000        "duplicate": False,
2001        "expressions": False,
2002        "action": False,
2003        "conflict_keys": False,
2004        "constraint": False,
2005    }
2006
2007
2008class Returning(Expression):
2009    arg_types = {"expressions": True, "into": False}
2010
2011
2012# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2013class Introducer(Expression):
2014    arg_types = {"this": True, "expression": True}
2015
2016
2017# national char, like n'utf8'
2018class National(Expression):
2019    pass
2020
2021
2022class LoadData(Expression):
2023    arg_types = {
2024        "this": True,
2025        "local": False,
2026        "overwrite": False,
2027        "inpath": True,
2028        "partition": False,
2029        "input_format": False,
2030        "serde": False,
2031    }
2032
2033
2034class Partition(Expression):
2035    arg_types = {"expressions": True}
2036
2037
2038class PartitionRange(Expression):
2039    arg_types = {"this": True, "expression": True}
2040
2041
2042class Fetch(Expression):
2043    arg_types = {
2044        "direction": False,
2045        "count": False,
2046        "percent": False,
2047        "with_ties": False,
2048    }
2049
2050
2051class Group(Expression):
2052    arg_types = {
2053        "expressions": False,
2054        "grouping_sets": False,
2055        "cube": False,
2056        "rollup": False,
2057        "totals": False,
2058        "all": False,
2059    }
2060
2061
2062class Lambda(Expression):
2063    arg_types = {"this": True, "expressions": True}
2064
2065
2066class Limit(Expression):
2067    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2068
2069
2070class Literal(Condition):
2071    arg_types = {"this": True, "is_string": True}
2072
2073    @property
2074    def hashable_args(self) -> t.Any:
2075        return (self.this, self.args.get("is_string"))
2076
2077    @classmethod
2078    def number(cls, number) -> Literal:
2079        return cls(this=str(number), is_string=False)
2080
2081    @classmethod
2082    def string(cls, string) -> Literal:
2083        return cls(this=str(string), is_string=True)
2084
2085    @property
2086    def output_name(self) -> str:
2087        return self.name
2088
2089
2090class Join(Expression):
2091    arg_types = {
2092        "this": True,
2093        "on": False,
2094        "side": False,
2095        "kind": False,
2096        "using": False,
2097        "method": False,
2098        "global": False,
2099        "hint": False,
2100    }
2101
2102    @property
2103    def method(self) -> str:
2104        return self.text("method").upper()
2105
2106    @property
2107    def kind(self) -> str:
2108        return self.text("kind").upper()
2109
2110    @property
2111    def side(self) -> str:
2112        return self.text("side").upper()
2113
2114    @property
2115    def hint(self) -> str:
2116        return self.text("hint").upper()
2117
2118    @property
2119    def alias_or_name(self) -> str:
2120        return self.this.alias_or_name
2121
2122    def on(
2123        self,
2124        *expressions: t.Optional[ExpOrStr],
2125        append: bool = True,
2126        dialect: DialectType = None,
2127        copy: bool = True,
2128        **opts,
2129    ) -> Join:
2130        """
2131        Append to or set the ON expressions.
2132
2133        Example:
2134            >>> import sqlglot
2135            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2136            'JOIN x ON y = 1'
2137
2138        Args:
2139            *expressions: the SQL code strings to parse.
2140                If an `Expression` instance is passed, it will be used as-is.
2141                Multiple expressions are combined with an AND operator.
2142            append: if `True`, AND the new expressions to any existing expression.
2143                Otherwise, this resets the expression.
2144            dialect: the dialect used to parse the input expressions.
2145            copy: if `False`, modify this expression instance in-place.
2146            opts: other options to use to parse the input expressions.
2147
2148        Returns:
2149            The modified Join expression.
2150        """
2151        join = _apply_conjunction_builder(
2152            *expressions,
2153            instance=self,
2154            arg="on",
2155            append=append,
2156            dialect=dialect,
2157            copy=copy,
2158            **opts,
2159        )
2160
2161        if join.kind == "CROSS":
2162            join.set("kind", None)
2163
2164        return join
2165
2166    def using(
2167        self,
2168        *expressions: t.Optional[ExpOrStr],
2169        append: bool = True,
2170        dialect: DialectType = None,
2171        copy: bool = True,
2172        **opts,
2173    ) -> Join:
2174        """
2175        Append to or set the USING expressions.
2176
2177        Example:
2178            >>> import sqlglot
2179            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2180            'JOIN x USING (foo, bla)'
2181
2182        Args:
2183            *expressions: the SQL code strings to parse.
2184                If an `Expression` instance is passed, it will be used as-is.
2185            append: if `True`, concatenate the new expressions to the existing "using" list.
2186                Otherwise, this resets the expression.
2187            dialect: the dialect used to parse the input expressions.
2188            copy: if `False`, modify this expression instance in-place.
2189            opts: other options to use to parse the input expressions.
2190
2191        Returns:
2192            The modified Join expression.
2193        """
2194        join = _apply_list_builder(
2195            *expressions,
2196            instance=self,
2197            arg="using",
2198            append=append,
2199            dialect=dialect,
2200            copy=copy,
2201            **opts,
2202        )
2203
2204        if join.kind == "CROSS":
2205            join.set("kind", None)
2206
2207        return join
2208
2209
2210class Lateral(UDTF):
2211    arg_types = {
2212        "this": True,
2213        "view": False,
2214        "outer": False,
2215        "alias": False,
2216        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2217    }
2218
2219
2220class MatchRecognize(Expression):
2221    arg_types = {
2222        "partition_by": False,
2223        "order": False,
2224        "measures": False,
2225        "rows": False,
2226        "after": False,
2227        "pattern": False,
2228        "define": False,
2229        "alias": False,
2230    }
2231
2232
2233# Clickhouse FROM FINAL modifier
2234# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2235class Final(Expression):
2236    pass
2237
2238
2239class Offset(Expression):
2240    arg_types = {"this": False, "expression": True, "expressions": False}
2241
2242
2243class Order(Expression):
2244    arg_types = {
2245        "this": False,
2246        "expressions": True,
2247        "interpolate": False,
2248        "siblings": False,
2249    }
2250
2251
2252# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2253class WithFill(Expression):
2254    arg_types = {"from": False, "to": False, "step": False}
2255
2256
2257# hive specific sorts
2258# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2259class Cluster(Order):
2260    pass
2261
2262
2263class Distribute(Order):
2264    pass
2265
2266
2267class Sort(Order):
2268    pass
2269
2270
2271class Ordered(Expression):
2272    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2273
2274
2275class Property(Expression):
2276    arg_types = {"this": True, "value": True}
2277
2278
2279class AlgorithmProperty(Property):
2280    arg_types = {"this": True}
2281
2282
2283class AutoIncrementProperty(Property):
2284    arg_types = {"this": True}
2285
2286
2287# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2288class AutoRefreshProperty(Property):
2289    arg_types = {"this": True}
2290
2291
2292class BackupProperty(Property):
2293    arg_types = {"this": True}
2294
2295
2296class BlockCompressionProperty(Property):
2297    arg_types = {
2298        "autotemp": False,
2299        "always": False,
2300        "default": False,
2301        "manual": False,
2302        "never": False,
2303    }
2304
2305
2306class CharacterSetProperty(Property):
2307    arg_types = {"this": True, "default": True}
2308
2309
2310class ChecksumProperty(Property):
2311    arg_types = {"on": False, "default": False}
2312
2313
2314class CollateProperty(Property):
2315    arg_types = {"this": True, "default": False}
2316
2317
2318class CopyGrantsProperty(Property):
2319    arg_types = {}
2320
2321
2322class DataBlocksizeProperty(Property):
2323    arg_types = {
2324        "size": False,
2325        "units": False,
2326        "minimum": False,
2327        "maximum": False,
2328        "default": False,
2329    }
2330
2331
2332class DefinerProperty(Property):
2333    arg_types = {"this": True}
2334
2335
2336class DistKeyProperty(Property):
2337    arg_types = {"this": True}
2338
2339
2340class DistStyleProperty(Property):
2341    arg_types = {"this": True}
2342
2343
2344class EngineProperty(Property):
2345    arg_types = {"this": True}
2346
2347
2348class HeapProperty(Property):
2349    arg_types = {}
2350
2351
2352class ToTableProperty(Property):
2353    arg_types = {"this": True}
2354
2355
2356class ExecuteAsProperty(Property):
2357    arg_types = {"this": True}
2358
2359
2360class ExternalProperty(Property):
2361    arg_types = {"this": False}
2362
2363
2364class FallbackProperty(Property):
2365    arg_types = {"no": True, "protection": False}
2366
2367
2368class FileFormatProperty(Property):
2369    arg_types = {"this": True}
2370
2371
2372class FreespaceProperty(Property):
2373    arg_types = {"this": True, "percent": False}
2374
2375
2376class GlobalProperty(Property):
2377    arg_types = {}
2378
2379
2380class IcebergProperty(Property):
2381    arg_types = {}
2382
2383
2384class InheritsProperty(Property):
2385    arg_types = {"expressions": True}
2386
2387
2388class InputModelProperty(Property):
2389    arg_types = {"this": True}
2390
2391
2392class OutputModelProperty(Property):
2393    arg_types = {"this": True}
2394
2395
2396class IsolatedLoadingProperty(Property):
2397    arg_types = {
2398        "no": False,
2399        "concurrent": False,
2400        "for_all": False,
2401        "for_insert": False,
2402        "for_none": False,
2403    }
2404
2405
2406class JournalProperty(Property):
2407    arg_types = {
2408        "no": False,
2409        "dual": False,
2410        "before": False,
2411        "local": False,
2412        "after": False,
2413    }
2414
2415
2416class LanguageProperty(Property):
2417    arg_types = {"this": True}
2418
2419
2420# spark ddl
2421class ClusteredByProperty(Property):
2422    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2423
2424
2425class DictProperty(Property):
2426    arg_types = {"this": True, "kind": True, "settings": False}
2427
2428
2429class DictSubProperty(Property):
2430    pass
2431
2432
2433class DictRange(Property):
2434    arg_types = {"this": True, "min": True, "max": True}
2435
2436
2437# Clickhouse CREATE ... ON CLUSTER modifier
2438# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2439class OnCluster(Property):
2440    arg_types = {"this": True}
2441
2442
2443class LikeProperty(Property):
2444    arg_types = {"this": True, "expressions": False}
2445
2446
2447class LocationProperty(Property):
2448    arg_types = {"this": True}
2449
2450
2451class LockProperty(Property):
2452    arg_types = {"this": True}
2453
2454
2455class LockingProperty(Property):
2456    arg_types = {
2457        "this": False,
2458        "kind": True,
2459        "for_or_in": False,
2460        "lock_type": True,
2461        "override": False,
2462    }
2463
2464
2465class LogProperty(Property):
2466    arg_types = {"no": True}
2467
2468
2469class MaterializedProperty(Property):
2470    arg_types = {"this": False}
2471
2472
2473class MergeBlockRatioProperty(Property):
2474    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2475
2476
2477class NoPrimaryIndexProperty(Property):
2478    arg_types = {}
2479
2480
2481class OnProperty(Property):
2482    arg_types = {"this": True}
2483
2484
2485class OnCommitProperty(Property):
2486    arg_types = {"delete": False}
2487
2488
2489class PartitionedByProperty(Property):
2490    arg_types = {"this": True}
2491
2492
2493# https://www.postgresql.org/docs/current/sql-createtable.html
2494class PartitionBoundSpec(Expression):
2495    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2496    arg_types = {
2497        "this": False,
2498        "expression": False,
2499        "from_expressions": False,
2500        "to_expressions": False,
2501    }
2502
2503
2504class PartitionedOfProperty(Property):
2505    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2506    arg_types = {"this": True, "expression": True}
2507
2508
2509class RemoteWithConnectionModelProperty(Property):
2510    arg_types = {"this": True}
2511
2512
2513class ReturnsProperty(Property):
2514    arg_types = {"this": True, "is_table": False, "table": False}
2515
2516
2517class RowFormatProperty(Property):
2518    arg_types = {"this": True}
2519
2520
2521class RowFormatDelimitedProperty(Property):
2522    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2523    arg_types = {
2524        "fields": False,
2525        "escaped": False,
2526        "collection_items": False,
2527        "map_keys": False,
2528        "lines": False,
2529        "null": False,
2530        "serde": False,
2531    }
2532
2533
2534class RowFormatSerdeProperty(Property):
2535    arg_types = {"this": True, "serde_properties": False}
2536
2537
2538# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2539class QueryTransform(Expression):
2540    arg_types = {
2541        "expressions": True,
2542        "command_script": True,
2543        "schema": False,
2544        "row_format_before": False,
2545        "record_writer": False,
2546        "row_format_after": False,
2547        "record_reader": False,
2548    }
2549
2550
2551class SampleProperty(Property):
2552    arg_types = {"this": True}
2553
2554
2555class SchemaCommentProperty(Property):
2556    arg_types = {"this": True}
2557
2558
2559class SerdeProperties(Property):
2560    arg_types = {"expressions": True}
2561
2562
2563class SetProperty(Property):
2564    arg_types = {"multi": True}
2565
2566
2567class SharingProperty(Property):
2568    arg_types = {"this": False}
2569
2570
2571class SetConfigProperty(Property):
2572    arg_types = {"this": True}
2573
2574
2575class SettingsProperty(Property):
2576    arg_types = {"expressions": True}
2577
2578
2579class SortKeyProperty(Property):
2580    arg_types = {"this": True, "compound": False}
2581
2582
2583class SqlReadWriteProperty(Property):
2584    arg_types = {"this": True}
2585
2586
2587class SqlSecurityProperty(Property):
2588    arg_types = {"definer": True}
2589
2590
2591class StabilityProperty(Property):
2592    arg_types = {"this": True}
2593
2594
2595class TemporaryProperty(Property):
2596    arg_types = {"this": False}
2597
2598
2599class TransformModelProperty(Property):
2600    arg_types = {"expressions": True}
2601
2602
2603class TransientProperty(Property):
2604    arg_types = {"this": False}
2605
2606
2607class UnloggedProperty(Property):
2608    arg_types = {}
2609
2610
2611class VolatileProperty(Property):
2612    arg_types = {"this": False}
2613
2614
2615class WithDataProperty(Property):
2616    arg_types = {"no": True, "statistics": False}
2617
2618
2619class WithJournalTableProperty(Property):
2620    arg_types = {"this": True}
2621
2622
2623class WithSystemVersioningProperty(Property):
2624    # this -> history table name, expression -> data consistency check
2625    arg_types = {"this": False, "expression": False}
2626
2627
2628class Properties(Expression):
2629    arg_types = {"expressions": True}
2630
2631    NAME_TO_PROPERTY = {
2632        "ALGORITHM": AlgorithmProperty,
2633        "AUTO_INCREMENT": AutoIncrementProperty,
2634        "CHARACTER SET": CharacterSetProperty,
2635        "CLUSTERED_BY": ClusteredByProperty,
2636        "COLLATE": CollateProperty,
2637        "COMMENT": SchemaCommentProperty,
2638        "DEFINER": DefinerProperty,
2639        "DISTKEY": DistKeyProperty,
2640        "DISTSTYLE": DistStyleProperty,
2641        "ENGINE": EngineProperty,
2642        "EXECUTE AS": ExecuteAsProperty,
2643        "FORMAT": FileFormatProperty,
2644        "LANGUAGE": LanguageProperty,
2645        "LOCATION": LocationProperty,
2646        "LOCK": LockProperty,
2647        "PARTITIONED_BY": PartitionedByProperty,
2648        "RETURNS": ReturnsProperty,
2649        "ROW_FORMAT": RowFormatProperty,
2650        "SORTKEY": SortKeyProperty,
2651    }
2652
2653    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2654
2655    # CREATE property locations
2656    # Form: schema specified
2657    #   create [POST_CREATE]
2658    #     table a [POST_NAME]
2659    #     (b int) [POST_SCHEMA]
2660    #     with ([POST_WITH])
2661    #     index (b) [POST_INDEX]
2662    #
2663    # Form: alias selection
2664    #   create [POST_CREATE]
2665    #     table a [POST_NAME]
2666    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2667    #     index (c) [POST_INDEX]
2668    class Location(AutoName):
2669        POST_CREATE = auto()
2670        POST_NAME = auto()
2671        POST_SCHEMA = auto()
2672        POST_WITH = auto()
2673        POST_ALIAS = auto()
2674        POST_EXPRESSION = auto()
2675        POST_INDEX = auto()
2676        UNSUPPORTED = auto()
2677
2678    @classmethod
2679    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2680        expressions = []
2681        for key, value in properties_dict.items():
2682            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2683            if property_cls:
2684                expressions.append(property_cls(this=convert(value)))
2685            else:
2686                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2687
2688        return cls(expressions=expressions)
2689
2690
2691class Qualify(Expression):
2692    pass
2693
2694
2695class InputOutputFormat(Expression):
2696    arg_types = {"input_format": False, "output_format": False}
2697
2698
2699# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2700class Return(Expression):
2701    pass
2702
2703
2704class Reference(Expression):
2705    arg_types = {"this": True, "expressions": False, "options": False}
2706
2707
2708class Tuple(Expression):
2709    arg_types = {"expressions": False}
2710
2711    def isin(
2712        self,
2713        *expressions: t.Any,
2714        query: t.Optional[ExpOrStr] = None,
2715        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2716        copy: bool = True,
2717        **opts,
2718    ) -> In:
2719        return In(
2720            this=maybe_copy(self, copy),
2721            expressions=[convert(e, copy=copy) for e in expressions],
2722            query=maybe_parse(query, copy=copy, **opts) if query else None,
2723            unnest=(
2724                Unnest(
2725                    expressions=[
2726                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2727                        for e in ensure_list(unnest)
2728                    ]
2729                )
2730                if unnest
2731                else None
2732            ),
2733        )
2734
2735
2736QUERY_MODIFIERS = {
2737    "match": False,
2738    "laterals": False,
2739    "joins": False,
2740    "connect": False,
2741    "pivots": False,
2742    "prewhere": False,
2743    "where": False,
2744    "group": False,
2745    "having": False,
2746    "qualify": False,
2747    "windows": False,
2748    "distribute": False,
2749    "sort": False,
2750    "cluster": False,
2751    "order": False,
2752    "limit": False,
2753    "offset": False,
2754    "locks": False,
2755    "sample": False,
2756    "settings": False,
2757    "format": False,
2758    "options": False,
2759}
2760
2761
2762# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
2763# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
2764class QueryOption(Expression):
2765    arg_types = {"this": True, "expression": False}
2766
2767
2768# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2769class WithTableHint(Expression):
2770    arg_types = {"expressions": True}
2771
2772
2773# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2774class IndexTableHint(Expression):
2775    arg_types = {"this": True, "expressions": False, "target": False}
2776
2777
2778# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2779class HistoricalData(Expression):
2780    arg_types = {"this": True, "kind": True, "expression": True}
2781
2782
2783class Table(Expression):
2784    arg_types = {
2785        "this": False,
2786        "alias": False,
2787        "db": False,
2788        "catalog": False,
2789        "laterals": False,
2790        "joins": False,
2791        "pivots": False,
2792        "hints": False,
2793        "system_time": False,
2794        "version": False,
2795        "format": False,
2796        "pattern": False,
2797        "ordinality": False,
2798        "when": False,
2799        "only": False,
2800    }
2801
2802    @property
2803    def name(self) -> str:
2804        if isinstance(self.this, Func):
2805            return ""
2806        return self.this.name
2807
2808    @property
2809    def db(self) -> str:
2810        return self.text("db")
2811
2812    @property
2813    def catalog(self) -> str:
2814        return self.text("catalog")
2815
2816    @property
2817    def selects(self) -> t.List[Expression]:
2818        return []
2819
2820    @property
2821    def named_selects(self) -> t.List[str]:
2822        return []
2823
2824    @property
2825    def parts(self) -> t.List[Expression]:
2826        """Return the parts of a table in order catalog, db, table."""
2827        parts: t.List[Expression] = []
2828
2829        for arg in ("catalog", "db", "this"):
2830            part = self.args.get(arg)
2831
2832            if isinstance(part, Dot):
2833                parts.extend(part.flatten())
2834            elif isinstance(part, Expression):
2835                parts.append(part)
2836
2837        return parts
2838
2839    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2840        parts = self.parts
2841        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2842        alias = self.args.get("alias")
2843        if alias:
2844            col = alias_(col, alias.this, copy=copy)
2845        return col
2846
2847
2848class Union(Query):
2849    arg_types = {
2850        "with": False,
2851        "this": True,
2852        "expression": True,
2853        "distinct": False,
2854        "by_name": False,
2855        **QUERY_MODIFIERS,
2856    }
2857
2858    def select(
2859        self,
2860        *expressions: t.Optional[ExpOrStr],
2861        append: bool = True,
2862        dialect: DialectType = None,
2863        copy: bool = True,
2864        **opts,
2865    ) -> Union:
2866        this = maybe_copy(self, copy)
2867        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2868        this.expression.unnest().select(
2869            *expressions, append=append, dialect=dialect, copy=False, **opts
2870        )
2871        return this
2872
2873    @property
2874    def named_selects(self) -> t.List[str]:
2875        return self.this.unnest().named_selects
2876
2877    @property
2878    def is_star(self) -> bool:
2879        return self.this.is_star or self.expression.is_star
2880
2881    @property
2882    def selects(self) -> t.List[Expression]:
2883        return self.this.unnest().selects
2884
2885    @property
2886    def left(self) -> Expression:
2887        return self.this
2888
2889    @property
2890    def right(self) -> Expression:
2891        return self.expression
2892
2893
2894class Except(Union):
2895    pass
2896
2897
2898class Intersect(Union):
2899    pass
2900
2901
2902class Unnest(UDTF):
2903    arg_types = {
2904        "expressions": True,
2905        "alias": False,
2906        "offset": False,
2907    }
2908
2909    @property
2910    def selects(self) -> t.List[Expression]:
2911        columns = super().selects
2912        offset = self.args.get("offset")
2913        if offset:
2914            columns = columns + [to_identifier("offset") if offset is True else offset]
2915        return columns
2916
2917
2918class Update(Expression):
2919    arg_types = {
2920        "with": False,
2921        "this": False,
2922        "expressions": True,
2923        "from": False,
2924        "where": False,
2925        "returning": False,
2926        "order": False,
2927        "limit": False,
2928    }
2929
2930
2931class Values(UDTF):
2932    arg_types = {"expressions": True, "alias": False}
2933
2934
2935class Var(Expression):
2936    pass
2937
2938
2939class Version(Expression):
2940    """
2941    Time travel, iceberg, bigquery etc
2942    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2943    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2944    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2945    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2946    this is either TIMESTAMP or VERSION
2947    kind is ("AS OF", "BETWEEN")
2948    """
2949
2950    arg_types = {"this": True, "kind": True, "expression": False}
2951
2952
2953class Schema(Expression):
2954    arg_types = {"this": False, "expressions": False}
2955
2956
2957# https://dev.mysql.com/doc/refman/8.0/en/select.html
2958# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2959class Lock(Expression):
2960    arg_types = {"update": True, "expressions": False, "wait": False}
2961
2962
2963class Select(Query):
2964    arg_types = {
2965        "with": False,
2966        "kind": False,
2967        "expressions": False,
2968        "hint": False,
2969        "distinct": False,
2970        "into": False,
2971        "from": False,
2972        **QUERY_MODIFIERS,
2973    }
2974
2975    def from_(
2976        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2977    ) -> Select:
2978        """
2979        Set the FROM expression.
2980
2981        Example:
2982            >>> Select().from_("tbl").select("x").sql()
2983            'SELECT x FROM tbl'
2984
2985        Args:
2986            expression : the SQL code strings to parse.
2987                If a `From` instance is passed, this is used as-is.
2988                If another `Expression` instance is passed, it will be wrapped in a `From`.
2989            dialect: the dialect used to parse the input expression.
2990            copy: if `False`, modify this expression instance in-place.
2991            opts: other options to use to parse the input expressions.
2992
2993        Returns:
2994            The modified Select expression.
2995        """
2996        return _apply_builder(
2997            expression=expression,
2998            instance=self,
2999            arg="from",
3000            into=From,
3001            prefix="FROM",
3002            dialect=dialect,
3003            copy=copy,
3004            **opts,
3005        )
3006
3007    def group_by(
3008        self,
3009        *expressions: t.Optional[ExpOrStr],
3010        append: bool = True,
3011        dialect: DialectType = None,
3012        copy: bool = True,
3013        **opts,
3014    ) -> Select:
3015        """
3016        Set the GROUP BY expression.
3017
3018        Example:
3019            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3020            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3021
3022        Args:
3023            *expressions: the SQL code strings to parse.
3024                If a `Group` instance is passed, this is used as-is.
3025                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3026                If nothing is passed in then a group by is not applied to the expression
3027            append: if `True`, add to any existing expressions.
3028                Otherwise, this flattens all the `Group` expression into a single expression.
3029            dialect: the dialect used to parse the input expression.
3030            copy: if `False`, modify this expression instance in-place.
3031            opts: other options to use to parse the input expressions.
3032
3033        Returns:
3034            The modified Select expression.
3035        """
3036        if not expressions:
3037            return self if not copy else self.copy()
3038
3039        return _apply_child_list_builder(
3040            *expressions,
3041            instance=self,
3042            arg="group",
3043            append=append,
3044            copy=copy,
3045            prefix="GROUP BY",
3046            into=Group,
3047            dialect=dialect,
3048            **opts,
3049        )
3050
3051    def order_by(
3052        self,
3053        *expressions: t.Optional[ExpOrStr],
3054        append: bool = True,
3055        dialect: DialectType = None,
3056        copy: bool = True,
3057        **opts,
3058    ) -> Select:
3059        """
3060        Set the ORDER BY expression.
3061
3062        Example:
3063            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
3064            'SELECT x FROM tbl ORDER BY x DESC'
3065
3066        Args:
3067            *expressions: the SQL code strings to parse.
3068                If a `Group` instance is passed, this is used as-is.
3069                If another `Expression` instance is passed, it will be wrapped in a `Order`.
3070            append: if `True`, add to any existing expressions.
3071                Otherwise, this flattens all the `Order` expression into a single expression.
3072            dialect: the dialect used to parse the input expression.
3073            copy: if `False`, modify this expression instance in-place.
3074            opts: other options to use to parse the input expressions.
3075
3076        Returns:
3077            The modified Select expression.
3078        """
3079        return _apply_child_list_builder(
3080            *expressions,
3081            instance=self,
3082            arg="order",
3083            append=append,
3084            copy=copy,
3085            prefix="ORDER BY",
3086            into=Order,
3087            dialect=dialect,
3088            **opts,
3089        )
3090
3091    def sort_by(
3092        self,
3093        *expressions: t.Optional[ExpOrStr],
3094        append: bool = True,
3095        dialect: DialectType = None,
3096        copy: bool = True,
3097        **opts,
3098    ) -> Select:
3099        """
3100        Set the SORT BY expression.
3101
3102        Example:
3103            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3104            'SELECT x FROM tbl SORT BY x DESC'
3105
3106        Args:
3107            *expressions: the SQL code strings to parse.
3108                If a `Group` instance is passed, this is used as-is.
3109                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3110            append: if `True`, add to any existing expressions.
3111                Otherwise, this flattens all the `Order` expression into a single expression.
3112            dialect: the dialect used to parse the input expression.
3113            copy: if `False`, modify this expression instance in-place.
3114            opts: other options to use to parse the input expressions.
3115
3116        Returns:
3117            The modified Select expression.
3118        """
3119        return _apply_child_list_builder(
3120            *expressions,
3121            instance=self,
3122            arg="sort",
3123            append=append,
3124            copy=copy,
3125            prefix="SORT BY",
3126            into=Sort,
3127            dialect=dialect,
3128            **opts,
3129        )
3130
3131    def cluster_by(
3132        self,
3133        *expressions: t.Optional[ExpOrStr],
3134        append: bool = True,
3135        dialect: DialectType = None,
3136        copy: bool = True,
3137        **opts,
3138    ) -> Select:
3139        """
3140        Set the CLUSTER BY expression.
3141
3142        Example:
3143            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3144            'SELECT x FROM tbl CLUSTER BY x DESC'
3145
3146        Args:
3147            *expressions: the SQL code strings to parse.
3148                If a `Group` instance is passed, this is used as-is.
3149                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3150            append: if `True`, add to any existing expressions.
3151                Otherwise, this flattens all the `Order` expression into a single expression.
3152            dialect: the dialect used to parse the input expression.
3153            copy: if `False`, modify this expression instance in-place.
3154            opts: other options to use to parse the input expressions.
3155
3156        Returns:
3157            The modified Select expression.
3158        """
3159        return _apply_child_list_builder(
3160            *expressions,
3161            instance=self,
3162            arg="cluster",
3163            append=append,
3164            copy=copy,
3165            prefix="CLUSTER BY",
3166            into=Cluster,
3167            dialect=dialect,
3168            **opts,
3169        )
3170
3171    def limit(
3172        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3173    ) -> Select:
3174        return _apply_builder(
3175            expression=expression,
3176            instance=self,
3177            arg="limit",
3178            into=Limit,
3179            prefix="LIMIT",
3180            dialect=dialect,
3181            copy=copy,
3182            into_arg="expression",
3183            **opts,
3184        )
3185
3186    def offset(
3187        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3188    ) -> Select:
3189        """
3190        Set the OFFSET expression.
3191
3192        Example:
3193            >>> Select().from_("tbl").select("x").offset(10).sql()
3194            'SELECT x FROM tbl OFFSET 10'
3195
3196        Args:
3197            expression: the SQL code string to parse.
3198                This can also be an integer.
3199                If a `Offset` instance is passed, this is used as-is.
3200                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3201            dialect: the dialect used to parse the input expression.
3202            copy: if `False`, modify this expression instance in-place.
3203            opts: other options to use to parse the input expressions.
3204
3205        Returns:
3206            The modified Select expression.
3207        """
3208        return _apply_builder(
3209            expression=expression,
3210            instance=self,
3211            arg="offset",
3212            into=Offset,
3213            prefix="OFFSET",
3214            dialect=dialect,
3215            copy=copy,
3216            into_arg="expression",
3217            **opts,
3218        )
3219
3220    def select(
3221        self,
3222        *expressions: t.Optional[ExpOrStr],
3223        append: bool = True,
3224        dialect: DialectType = None,
3225        copy: bool = True,
3226        **opts,
3227    ) -> Select:
3228        return _apply_list_builder(
3229            *expressions,
3230            instance=self,
3231            arg="expressions",
3232            append=append,
3233            dialect=dialect,
3234            into=Expression,
3235            copy=copy,
3236            **opts,
3237        )
3238
3239    def lateral(
3240        self,
3241        *expressions: t.Optional[ExpOrStr],
3242        append: bool = True,
3243        dialect: DialectType = None,
3244        copy: bool = True,
3245        **opts,
3246    ) -> Select:
3247        """
3248        Append to or set the LATERAL expressions.
3249
3250        Example:
3251            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3252            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3253
3254        Args:
3255            *expressions: the SQL code strings to parse.
3256                If an `Expression` instance is passed, it will be used as-is.
3257            append: if `True`, add to any existing expressions.
3258                Otherwise, this resets the expressions.
3259            dialect: the dialect used to parse the input expressions.
3260            copy: if `False`, modify this expression instance in-place.
3261            opts: other options to use to parse the input expressions.
3262
3263        Returns:
3264            The modified Select expression.
3265        """
3266        return _apply_list_builder(
3267            *expressions,
3268            instance=self,
3269            arg="laterals",
3270            append=append,
3271            into=Lateral,
3272            prefix="LATERAL VIEW",
3273            dialect=dialect,
3274            copy=copy,
3275            **opts,
3276        )
3277
3278    def join(
3279        self,
3280        expression: ExpOrStr,
3281        on: t.Optional[ExpOrStr] = None,
3282        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3283        append: bool = True,
3284        join_type: t.Optional[str] = None,
3285        join_alias: t.Optional[Identifier | str] = None,
3286        dialect: DialectType = None,
3287        copy: bool = True,
3288        **opts,
3289    ) -> Select:
3290        """
3291        Append to or set the JOIN expressions.
3292
3293        Example:
3294            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3295            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3296
3297            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3298            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3299
3300            Use `join_type` to change the type of join:
3301
3302            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3303            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3304
3305        Args:
3306            expression: the SQL code string to parse.
3307                If an `Expression` instance is passed, it will be used as-is.
3308            on: optionally specify the join "on" criteria as a SQL string.
3309                If an `Expression` instance is passed, it will be used as-is.
3310            using: optionally specify the join "using" criteria as a SQL string.
3311                If an `Expression` instance is passed, it will be used as-is.
3312            append: if `True`, add to any existing expressions.
3313                Otherwise, this resets the expressions.
3314            join_type: if set, alter the parsed join type.
3315            join_alias: an optional alias for the joined source.
3316            dialect: the dialect used to parse the input expressions.
3317            copy: if `False`, modify this expression instance in-place.
3318            opts: other options to use to parse the input expressions.
3319
3320        Returns:
3321            Select: the modified expression.
3322        """
3323        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3324
3325        try:
3326            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3327        except ParseError:
3328            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3329
3330        join = expression if isinstance(expression, Join) else Join(this=expression)
3331
3332        if isinstance(join.this, Select):
3333            join.this.replace(join.this.subquery())
3334
3335        if join_type:
3336            method: t.Optional[Token]
3337            side: t.Optional[Token]
3338            kind: t.Optional[Token]
3339
3340            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3341
3342            if method:
3343                join.set("method", method.text)
3344            if side:
3345                join.set("side", side.text)
3346            if kind:
3347                join.set("kind", kind.text)
3348
3349        if on:
3350            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3351            join.set("on", on)
3352
3353        if using:
3354            join = _apply_list_builder(
3355                *ensure_list(using),
3356                instance=join,
3357                arg="using",
3358                append=append,
3359                copy=copy,
3360                into=Identifier,
3361                **opts,
3362            )
3363
3364        if join_alias:
3365            join.set("this", alias_(join.this, join_alias, table=True))
3366
3367        return _apply_list_builder(
3368            join,
3369            instance=self,
3370            arg="joins",
3371            append=append,
3372            copy=copy,
3373            **opts,
3374        )
3375
3376    def where(
3377        self,
3378        *expressions: t.Optional[ExpOrStr],
3379        append: bool = True,
3380        dialect: DialectType = None,
3381        copy: bool = True,
3382        **opts,
3383    ) -> Select:
3384        """
3385        Append to or set the WHERE expressions.
3386
3387        Example:
3388            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3389            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3390
3391        Args:
3392            *expressions: the SQL code strings to parse.
3393                If an `Expression` instance is passed, it will be used as-is.
3394                Multiple expressions are combined with an AND operator.
3395            append: if `True`, AND the new expressions to any existing expression.
3396                Otherwise, this resets the expression.
3397            dialect: the dialect used to parse the input expressions.
3398            copy: if `False`, modify this expression instance in-place.
3399            opts: other options to use to parse the input expressions.
3400
3401        Returns:
3402            Select: the modified expression.
3403        """
3404        return _apply_conjunction_builder(
3405            *expressions,
3406            instance=self,
3407            arg="where",
3408            append=append,
3409            into=Where,
3410            dialect=dialect,
3411            copy=copy,
3412            **opts,
3413        )
3414
3415    def having(
3416        self,
3417        *expressions: t.Optional[ExpOrStr],
3418        append: bool = True,
3419        dialect: DialectType = None,
3420        copy: bool = True,
3421        **opts,
3422    ) -> Select:
3423        """
3424        Append to or set the HAVING expressions.
3425
3426        Example:
3427            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3428            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3429
3430        Args:
3431            *expressions: the SQL code strings to parse.
3432                If an `Expression` instance is passed, it will be used as-is.
3433                Multiple expressions are combined with an AND operator.
3434            append: if `True`, AND the new expressions to any existing expression.
3435                Otherwise, this resets the expression.
3436            dialect: the dialect used to parse the input expressions.
3437            copy: if `False`, modify this expression instance in-place.
3438            opts: other options to use to parse the input expressions.
3439
3440        Returns:
3441            The modified Select expression.
3442        """
3443        return _apply_conjunction_builder(
3444            *expressions,
3445            instance=self,
3446            arg="having",
3447            append=append,
3448            into=Having,
3449            dialect=dialect,
3450            copy=copy,
3451            **opts,
3452        )
3453
3454    def window(
3455        self,
3456        *expressions: t.Optional[ExpOrStr],
3457        append: bool = True,
3458        dialect: DialectType = None,
3459        copy: bool = True,
3460        **opts,
3461    ) -> Select:
3462        return _apply_list_builder(
3463            *expressions,
3464            instance=self,
3465            arg="windows",
3466            append=append,
3467            into=Window,
3468            dialect=dialect,
3469            copy=copy,
3470            **opts,
3471        )
3472
3473    def qualify(
3474        self,
3475        *expressions: t.Optional[ExpOrStr],
3476        append: bool = True,
3477        dialect: DialectType = None,
3478        copy: bool = True,
3479        **opts,
3480    ) -> Select:
3481        return _apply_conjunction_builder(
3482            *expressions,
3483            instance=self,
3484            arg="qualify",
3485            append=append,
3486            into=Qualify,
3487            dialect=dialect,
3488            copy=copy,
3489            **opts,
3490        )
3491
3492    def distinct(
3493        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3494    ) -> Select:
3495        """
3496        Set the OFFSET expression.
3497
3498        Example:
3499            >>> Select().from_("tbl").select("x").distinct().sql()
3500            'SELECT DISTINCT x FROM tbl'
3501
3502        Args:
3503            ons: the expressions to distinct on
3504            distinct: whether the Select should be distinct
3505            copy: if `False`, modify this expression instance in-place.
3506
3507        Returns:
3508            Select: the modified expression.
3509        """
3510        instance = maybe_copy(self, copy)
3511        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3512        instance.set("distinct", Distinct(on=on) if distinct else None)
3513        return instance
3514
3515    def ctas(
3516        self,
3517        table: ExpOrStr,
3518        properties: t.Optional[t.Dict] = None,
3519        dialect: DialectType = None,
3520        copy: bool = True,
3521        **opts,
3522    ) -> Create:
3523        """
3524        Convert this expression to a CREATE TABLE AS statement.
3525
3526        Example:
3527            >>> Select().select("*").from_("tbl").ctas("x").sql()
3528            'CREATE TABLE x AS SELECT * FROM tbl'
3529
3530        Args:
3531            table: the SQL code string to parse as the table name.
3532                If another `Expression` instance is passed, it will be used as-is.
3533            properties: an optional mapping of table properties
3534            dialect: the dialect used to parse the input table.
3535            copy: if `False`, modify this expression instance in-place.
3536            opts: other options to use to parse the input table.
3537
3538        Returns:
3539            The new Create expression.
3540        """
3541        instance = maybe_copy(self, copy)
3542        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3543
3544        properties_expression = None
3545        if properties:
3546            properties_expression = Properties.from_dict(properties)
3547
3548        return Create(
3549            this=table_expression,
3550            kind="TABLE",
3551            expression=instance,
3552            properties=properties_expression,
3553        )
3554
3555    def lock(self, update: bool = True, copy: bool = True) -> Select:
3556        """
3557        Set the locking read mode for this expression.
3558
3559        Examples:
3560            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3561            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3562
3563            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3564            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3565
3566        Args:
3567            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3568            copy: if `False`, modify this expression instance in-place.
3569
3570        Returns:
3571            The modified expression.
3572        """
3573        inst = maybe_copy(self, copy)
3574        inst.set("locks", [Lock(update=update)])
3575
3576        return inst
3577
3578    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3579        """
3580        Set hints for this expression.
3581
3582        Examples:
3583            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3584            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3585
3586        Args:
3587            hints: The SQL code strings to parse as the hints.
3588                If an `Expression` instance is passed, it will be used as-is.
3589            dialect: The dialect used to parse the hints.
3590            copy: If `False`, modify this expression instance in-place.
3591
3592        Returns:
3593            The modified expression.
3594        """
3595        inst = maybe_copy(self, copy)
3596        inst.set(
3597            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3598        )
3599
3600        return inst
3601
3602    @property
3603    def named_selects(self) -> t.List[str]:
3604        return [e.output_name for e in self.expressions if e.alias_or_name]
3605
3606    @property
3607    def is_star(self) -> bool:
3608        return any(expression.is_star for expression in self.expressions)
3609
3610    @property
3611    def selects(self) -> t.List[Expression]:
3612        return self.expressions
3613
3614
3615UNWRAPPED_QUERIES = (Select, Union)
3616
3617
3618class Subquery(DerivedTable, Query):
3619    arg_types = {
3620        "this": True,
3621        "alias": False,
3622        "with": False,
3623        **QUERY_MODIFIERS,
3624    }
3625
3626    def unnest(self):
3627        """Returns the first non subquery."""
3628        expression = self
3629        while isinstance(expression, Subquery):
3630            expression = expression.this
3631        return expression
3632
3633    def unwrap(self) -> Subquery:
3634        expression = self
3635        while expression.same_parent and expression.is_wrapper:
3636            expression = t.cast(Subquery, expression.parent)
3637        return expression
3638
3639    def select(
3640        self,
3641        *expressions: t.Optional[ExpOrStr],
3642        append: bool = True,
3643        dialect: DialectType = None,
3644        copy: bool = True,
3645        **opts,
3646    ) -> Subquery:
3647        this = maybe_copy(self, copy)
3648        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3649        return this
3650
3651    @property
3652    def is_wrapper(self) -> bool:
3653        """
3654        Whether this Subquery acts as a simple wrapper around another expression.
3655
3656        SELECT * FROM (((SELECT * FROM t)))
3657                      ^
3658                      This corresponds to a "wrapper" Subquery node
3659        """
3660        return all(v is None for k, v in self.args.items() if k != "this")
3661
3662    @property
3663    def is_star(self) -> bool:
3664        return self.this.is_star
3665
3666    @property
3667    def output_name(self) -> str:
3668        return self.alias
3669
3670
3671class TableSample(Expression):
3672    arg_types = {
3673        "this": False,
3674        "expressions": False,
3675        "method": False,
3676        "bucket_numerator": False,
3677        "bucket_denominator": False,
3678        "bucket_field": False,
3679        "percent": False,
3680        "rows": False,
3681        "size": False,
3682        "seed": False,
3683    }
3684
3685
3686class Tag(Expression):
3687    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3688
3689    arg_types = {
3690        "this": False,
3691        "prefix": False,
3692        "postfix": False,
3693    }
3694
3695
3696# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3697# https://duckdb.org/docs/sql/statements/pivot
3698class Pivot(Expression):
3699    arg_types = {
3700        "this": False,
3701        "alias": False,
3702        "expressions": False,
3703        "field": False,
3704        "unpivot": False,
3705        "using": False,
3706        "group": False,
3707        "columns": False,
3708        "include_nulls": False,
3709    }
3710
3711    @property
3712    def unpivot(self) -> bool:
3713        return bool(self.args.get("unpivot"))
3714
3715
3716class Window(Condition):
3717    arg_types = {
3718        "this": True,
3719        "partition_by": False,
3720        "order": False,
3721        "spec": False,
3722        "alias": False,
3723        "over": False,
3724        "first": False,
3725    }
3726
3727
3728class WindowSpec(Expression):
3729    arg_types = {
3730        "kind": False,
3731        "start": False,
3732        "start_side": False,
3733        "end": False,
3734        "end_side": False,
3735    }
3736
3737
3738class PreWhere(Expression):
3739    pass
3740
3741
3742class Where(Expression):
3743    pass
3744
3745
3746class Star(Expression):
3747    arg_types = {"except": False, "replace": False}
3748
3749    @property
3750    def name(self) -> str:
3751        return "*"
3752
3753    @property
3754    def output_name(self) -> str:
3755        return self.name
3756
3757
3758class Parameter(Condition):
3759    arg_types = {"this": True, "expression": False}
3760
3761
3762class SessionParameter(Condition):
3763    arg_types = {"this": True, "kind": False}
3764
3765
3766class Placeholder(Condition):
3767    arg_types = {"this": False, "kind": False}
3768
3769
3770class Null(Condition):
3771    arg_types: t.Dict[str, t.Any] = {}
3772
3773    @property
3774    def name(self) -> str:
3775        return "NULL"
3776
3777
3778class Boolean(Condition):
3779    pass
3780
3781
3782class DataTypeParam(Expression):
3783    arg_types = {"this": True, "expression": False}
3784
3785    @property
3786    def name(self) -> str:
3787        return self.this.name
3788
3789
3790class DataType(Expression):
3791    arg_types = {
3792        "this": True,
3793        "expressions": False,
3794        "nested": False,
3795        "values": False,
3796        "prefix": False,
3797        "kind": False,
3798    }
3799
3800    class Type(AutoName):
3801        ARRAY = auto()
3802        AGGREGATEFUNCTION = auto()
3803        SIMPLEAGGREGATEFUNCTION = auto()
3804        BIGDECIMAL = auto()
3805        BIGINT = auto()
3806        BIGSERIAL = auto()
3807        BINARY = auto()
3808        BIT = auto()
3809        BOOLEAN = auto()
3810        BPCHAR = auto()
3811        CHAR = auto()
3812        DATE = auto()
3813        DATE32 = auto()
3814        DATEMULTIRANGE = auto()
3815        DATERANGE = auto()
3816        DATETIME = auto()
3817        DATETIME64 = auto()
3818        DECIMAL = auto()
3819        DOUBLE = auto()
3820        ENUM = auto()
3821        ENUM8 = auto()
3822        ENUM16 = auto()
3823        FIXEDSTRING = auto()
3824        FLOAT = auto()
3825        GEOGRAPHY = auto()
3826        GEOMETRY = auto()
3827        HLLSKETCH = auto()
3828        HSTORE = auto()
3829        IMAGE = auto()
3830        INET = auto()
3831        INT = auto()
3832        INT128 = auto()
3833        INT256 = auto()
3834        INT4MULTIRANGE = auto()
3835        INT4RANGE = auto()
3836        INT8MULTIRANGE = auto()
3837        INT8RANGE = auto()
3838        INTERVAL = auto()
3839        IPADDRESS = auto()
3840        IPPREFIX = auto()
3841        IPV4 = auto()
3842        IPV6 = auto()
3843        JSON = auto()
3844        JSONB = auto()
3845        LONGBLOB = auto()
3846        LONGTEXT = auto()
3847        LOWCARDINALITY = auto()
3848        MAP = auto()
3849        MEDIUMBLOB = auto()
3850        MEDIUMINT = auto()
3851        MEDIUMTEXT = auto()
3852        MONEY = auto()
3853        NAME = auto()
3854        NCHAR = auto()
3855        NESTED = auto()
3856        NULL = auto()
3857        NULLABLE = auto()
3858        NUMMULTIRANGE = auto()
3859        NUMRANGE = auto()
3860        NVARCHAR = auto()
3861        OBJECT = auto()
3862        ROWVERSION = auto()
3863        SERIAL = auto()
3864        SET = auto()
3865        SMALLINT = auto()
3866        SMALLMONEY = auto()
3867        SMALLSERIAL = auto()
3868        STRUCT = auto()
3869        SUPER = auto()
3870        TEXT = auto()
3871        TINYBLOB = auto()
3872        TINYTEXT = auto()
3873        TIME = auto()
3874        TIMETZ = auto()
3875        TIMESTAMP = auto()
3876        TIMESTAMPLTZ = auto()
3877        TIMESTAMPTZ = auto()
3878        TIMESTAMP_S = auto()
3879        TIMESTAMP_MS = auto()
3880        TIMESTAMP_NS = auto()
3881        TINYINT = auto()
3882        TSMULTIRANGE = auto()
3883        TSRANGE = auto()
3884        TSTZMULTIRANGE = auto()
3885        TSTZRANGE = auto()
3886        UBIGINT = auto()
3887        UINT = auto()
3888        UINT128 = auto()
3889        UINT256 = auto()
3890        UMEDIUMINT = auto()
3891        UDECIMAL = auto()
3892        UNIQUEIDENTIFIER = auto()
3893        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3894        USERDEFINED = "USER-DEFINED"
3895        USMALLINT = auto()
3896        UTINYINT = auto()
3897        UUID = auto()
3898        VARBINARY = auto()
3899        VARCHAR = auto()
3900        VARIANT = auto()
3901        XML = auto()
3902        YEAR = auto()
3903
3904    STRUCT_TYPES = {
3905        Type.NESTED,
3906        Type.OBJECT,
3907        Type.STRUCT,
3908    }
3909
3910    NESTED_TYPES = {
3911        *STRUCT_TYPES,
3912        Type.ARRAY,
3913        Type.MAP,
3914    }
3915
3916    TEXT_TYPES = {
3917        Type.CHAR,
3918        Type.NCHAR,
3919        Type.NVARCHAR,
3920        Type.TEXT,
3921        Type.VARCHAR,
3922        Type.NAME,
3923    }
3924
3925    INTEGER_TYPES = {
3926        Type.BIGINT,
3927        Type.BIT,
3928        Type.INT,
3929        Type.INT128,
3930        Type.INT256,
3931        Type.MEDIUMINT,
3932        Type.SMALLINT,
3933        Type.TINYINT,
3934        Type.UBIGINT,
3935        Type.UINT,
3936        Type.UINT128,
3937        Type.UINT256,
3938        Type.UMEDIUMINT,
3939        Type.USMALLINT,
3940        Type.UTINYINT,
3941    }
3942
3943    FLOAT_TYPES = {
3944        Type.DOUBLE,
3945        Type.FLOAT,
3946    }
3947
3948    REAL_TYPES = {
3949        *FLOAT_TYPES,
3950        Type.BIGDECIMAL,
3951        Type.DECIMAL,
3952        Type.MONEY,
3953        Type.SMALLMONEY,
3954        Type.UDECIMAL,
3955    }
3956
3957    NUMERIC_TYPES = {
3958        *INTEGER_TYPES,
3959        *REAL_TYPES,
3960    }
3961
3962    TEMPORAL_TYPES = {
3963        Type.DATE,
3964        Type.DATE32,
3965        Type.DATETIME,
3966        Type.DATETIME64,
3967        Type.TIME,
3968        Type.TIMESTAMP,
3969        Type.TIMESTAMPLTZ,
3970        Type.TIMESTAMPTZ,
3971        Type.TIMESTAMP_MS,
3972        Type.TIMESTAMP_NS,
3973        Type.TIMESTAMP_S,
3974        Type.TIMETZ,
3975    }
3976
3977    @classmethod
3978    def build(
3979        cls,
3980        dtype: DATA_TYPE,
3981        dialect: DialectType = None,
3982        udt: bool = False,
3983        copy: bool = True,
3984        **kwargs,
3985    ) -> DataType:
3986        """
3987        Constructs a DataType object.
3988
3989        Args:
3990            dtype: the data type of interest.
3991            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3992            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3993                DataType, thus creating a user-defined type.
3994            copy: whether to copy the data type.
3995            kwargs: additional arguments to pass in the constructor of DataType.
3996
3997        Returns:
3998            The constructed DataType object.
3999        """
4000        from sqlglot import parse_one
4001
4002        if isinstance(dtype, str):
4003            if dtype.upper() == "UNKNOWN":
4004                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4005
4006            try:
4007                data_type_exp = parse_one(
4008                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4009                )
4010            except ParseError:
4011                if udt:
4012                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4013                raise
4014        elif isinstance(dtype, DataType.Type):
4015            data_type_exp = DataType(this=dtype)
4016        elif isinstance(dtype, DataType):
4017            return maybe_copy(dtype, copy)
4018        else:
4019            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4020
4021        return DataType(**{**data_type_exp.args, **kwargs})
4022
4023    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4024        """
4025        Checks whether this DataType matches one of the provided data types. Nested types or precision
4026        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4027
4028        Args:
4029            dtypes: the data types to compare this DataType to.
4030
4031        Returns:
4032            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4033        """
4034        for dtype in dtypes:
4035            other = DataType.build(dtype, copy=False, udt=True)
4036
4037            if (
4038                other.expressions
4039                or self.this == DataType.Type.USERDEFINED
4040                or other.this == DataType.Type.USERDEFINED
4041            ):
4042                matches = self == other
4043            else:
4044                matches = self.this == other.this
4045
4046            if matches:
4047                return True
4048        return False
4049
4050
4051DATA_TYPE = t.Union[str, DataType, DataType.Type]
4052
4053
4054# https://www.postgresql.org/docs/15/datatype-pseudo.html
4055class PseudoType(DataType):
4056    arg_types = {"this": True}
4057
4058
4059# https://www.postgresql.org/docs/15/datatype-oid.html
4060class ObjectIdentifier(DataType):
4061    arg_types = {"this": True}
4062
4063
4064# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4065class SubqueryPredicate(Predicate):
4066    pass
4067
4068
4069class All(SubqueryPredicate):
4070    pass
4071
4072
4073class Any(SubqueryPredicate):
4074    pass
4075
4076
4077class Exists(SubqueryPredicate):
4078    pass
4079
4080
4081# Commands to interact with the databases or engines. For most of the command
4082# expressions we parse whatever comes after the command's name as a string.
4083class Command(Expression):
4084    arg_types = {"this": True, "expression": False}
4085
4086
4087class Transaction(Expression):
4088    arg_types = {"this": False, "modes": False, "mark": False}
4089
4090
4091class Commit(Expression):
4092    arg_types = {"chain": False, "this": False, "durability": False}
4093
4094
4095class Rollback(Expression):
4096    arg_types = {"savepoint": False, "this": False}
4097
4098
4099class AlterTable(Expression):
4100    arg_types = {
4101        "this": True,
4102        "actions": True,
4103        "exists": False,
4104        "only": False,
4105        "options": False,
4106    }
4107
4108
4109class AddConstraint(Expression):
4110    arg_types = {"expressions": True}
4111
4112
4113class DropPartition(Expression):
4114    arg_types = {"expressions": True, "exists": False}
4115
4116
4117# Binary expressions like (ADD a b)
4118class Binary(Condition):
4119    arg_types = {"this": True, "expression": True}
4120
4121    @property
4122    def left(self) -> Expression:
4123        return self.this
4124
4125    @property
4126    def right(self) -> Expression:
4127        return self.expression
4128
4129
4130class Add(Binary):
4131    pass
4132
4133
4134class Connector(Binary):
4135    pass
4136
4137
4138class And(Connector):
4139    pass
4140
4141
4142class Or(Connector):
4143    pass
4144
4145
4146class BitwiseAnd(Binary):
4147    pass
4148
4149
4150class BitwiseLeftShift(Binary):
4151    pass
4152
4153
4154class BitwiseOr(Binary):
4155    pass
4156
4157
4158class BitwiseRightShift(Binary):
4159    pass
4160
4161
4162class BitwiseXor(Binary):
4163    pass
4164
4165
4166class Div(Binary):
4167    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4168
4169
4170class Overlaps(Binary):
4171    pass
4172
4173
4174class Dot(Binary):
4175    @property
4176    def is_star(self) -> bool:
4177        return self.expression.is_star
4178
4179    @property
4180    def name(self) -> str:
4181        return self.expression.name
4182
4183    @property
4184    def output_name(self) -> str:
4185        return self.name
4186
4187    @classmethod
4188    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4189        """Build a Dot object with a sequence of expressions."""
4190        if len(expressions) < 2:
4191            raise ValueError("Dot requires >= 2 expressions.")
4192
4193        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4194
4195    @property
4196    def parts(self) -> t.List[Expression]:
4197        """Return the parts of a table / column in order catalog, db, table."""
4198        this, *parts = self.flatten()
4199
4200        parts.reverse()
4201
4202        for arg in ("this", "table", "db", "catalog"):
4203            part = this.args.get(arg)
4204
4205            if isinstance(part, Expression):
4206                parts.append(part)
4207
4208        parts.reverse()
4209        return parts
4210
4211
4212class DPipe(Binary):
4213    arg_types = {"this": True, "expression": True, "safe": False}
4214
4215
4216class EQ(Binary, Predicate):
4217    pass
4218
4219
4220class NullSafeEQ(Binary, Predicate):
4221    pass
4222
4223
4224class NullSafeNEQ(Binary, Predicate):
4225    pass
4226
4227
4228# Represents e.g. := in DuckDB which is mostly used for setting parameters
4229class PropertyEQ(Binary):
4230    pass
4231
4232
4233class Distance(Binary):
4234    pass
4235
4236
4237class Escape(Binary):
4238    pass
4239
4240
4241class Glob(Binary, Predicate):
4242    pass
4243
4244
4245class GT(Binary, Predicate):
4246    pass
4247
4248
4249class GTE(Binary, Predicate):
4250    pass
4251
4252
4253class ILike(Binary, Predicate):
4254    pass
4255
4256
4257class ILikeAny(Binary, Predicate):
4258    pass
4259
4260
4261class IntDiv(Binary):
4262    pass
4263
4264
4265class Is(Binary, Predicate):
4266    pass
4267
4268
4269class Kwarg(Binary):
4270    """Kwarg in special functions like func(kwarg => y)."""
4271
4272
4273class Like(Binary, Predicate):
4274    pass
4275
4276
4277class LikeAny(Binary, Predicate):
4278    pass
4279
4280
4281class LT(Binary, Predicate):
4282    pass
4283
4284
4285class LTE(Binary, Predicate):
4286    pass
4287
4288
4289class Mod(Binary):
4290    pass
4291
4292
4293class Mul(Binary):
4294    pass
4295
4296
4297class NEQ(Binary, Predicate):
4298    pass
4299
4300
4301# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4302class Operator(Binary):
4303    arg_types = {"this": True, "operator": True, "expression": True}
4304
4305
4306class SimilarTo(Binary, Predicate):
4307    pass
4308
4309
4310class Slice(Binary):
4311    arg_types = {"this": False, "expression": False}
4312
4313
4314class Sub(Binary):
4315    pass
4316
4317
4318# Unary Expressions
4319# (NOT a)
4320class Unary(Condition):
4321    pass
4322
4323
4324class BitwiseNot(Unary):
4325    pass
4326
4327
4328class Not(Unary):
4329    pass
4330
4331
4332class Paren(Unary):
4333    @property
4334    def output_name(self) -> str:
4335        return self.this.name
4336
4337
4338class Neg(Unary):
4339    pass
4340
4341
4342class Alias(Expression):
4343    arg_types = {"this": True, "alias": False}
4344
4345    @property
4346    def output_name(self) -> str:
4347        return self.alias
4348
4349
4350# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4351# other dialects require identifiers. This enables us to transpile between them easily.
4352class PivotAlias(Alias):
4353    pass
4354
4355
4356class Aliases(Expression):
4357    arg_types = {"this": True, "expressions": True}
4358
4359    @property
4360    def aliases(self):
4361        return self.expressions
4362
4363
4364# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4365class AtIndex(Expression):
4366    arg_types = {"this": True, "expression": True}
4367
4368
4369class AtTimeZone(Expression):
4370    arg_types = {"this": True, "zone": True}
4371
4372
4373class FromTimeZone(Expression):
4374    arg_types = {"this": True, "zone": True}
4375
4376
4377class Between(Predicate):
4378    arg_types = {"this": True, "low": True, "high": True}
4379
4380
4381class Bracket(Condition):
4382    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4383    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4384
4385    @property
4386    def output_name(self) -> str:
4387        if len(self.expressions) == 1:
4388            return self.expressions[0].output_name
4389
4390        return super().output_name
4391
4392
4393class Distinct(Expression):
4394    arg_types = {"expressions": False, "on": False}
4395
4396
4397class In(Predicate):
4398    arg_types = {
4399        "this": True,
4400        "expressions": False,
4401        "query": False,
4402        "unnest": False,
4403        "field": False,
4404        "is_global": False,
4405    }
4406
4407
4408# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4409class ForIn(Expression):
4410    arg_types = {"this": True, "expression": True}
4411
4412
4413class TimeUnit(Expression):
4414    """Automatically converts unit arg into a var."""
4415
4416    arg_types = {"unit": False}
4417
4418    UNABBREVIATED_UNIT_NAME = {
4419        "D": "DAY",
4420        "H": "HOUR",
4421        "M": "MINUTE",
4422        "MS": "MILLISECOND",
4423        "NS": "NANOSECOND",
4424        "Q": "QUARTER",
4425        "S": "SECOND",
4426        "US": "MICROSECOND",
4427        "W": "WEEK",
4428        "Y": "YEAR",
4429    }
4430
4431    VAR_LIKE = (Column, Literal, Var)
4432
4433    def __init__(self, **args):
4434        unit = args.get("unit")
4435        if isinstance(unit, self.VAR_LIKE):
4436            args["unit"] = Var(
4437                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4438            )
4439        elif isinstance(unit, Week):
4440            unit.set("this", Var(this=unit.this.name.upper()))
4441
4442        super().__init__(**args)
4443
4444    @property
4445    def unit(self) -> t.Optional[Var]:
4446        return self.args.get("unit")
4447
4448
4449class IntervalOp(TimeUnit):
4450    arg_types = {"unit": True, "expression": True}
4451
4452    def interval(self):
4453        return Interval(
4454            this=self.expression.copy(),
4455            unit=self.unit.copy(),
4456        )
4457
4458
4459# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4460# https://trino.io/docs/current/language/types.html#interval-day-to-second
4461# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4462class IntervalSpan(DataType):
4463    arg_types = {"this": True, "expression": True}
4464
4465
4466class Interval(TimeUnit):
4467    arg_types = {"this": False, "unit": False}
4468
4469
4470class IgnoreNulls(Expression):
4471    pass
4472
4473
4474class RespectNulls(Expression):
4475    pass
4476
4477
4478# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4479class HavingMax(Expression):
4480    arg_types = {"this": True, "expression": True, "max": True}
4481
4482
4483# Functions
4484class Func(Condition):
4485    """
4486    The base class for all function expressions.
4487
4488    Attributes:
4489        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4490            treated as a variable length argument and the argument's value will be stored as a list.
4491        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4492            function expression. These values are used to map this node to a name during parsing as
4493            well as to provide the function's name during SQL string generation. By default the SQL
4494            name is set to the expression's class name transformed to snake case.
4495    """
4496
4497    is_var_len_args = False
4498
4499    @classmethod
4500    def from_arg_list(cls, args):
4501        if cls.is_var_len_args:
4502            all_arg_keys = list(cls.arg_types)
4503            # If this function supports variable length argument treat the last argument as such.
4504            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4505            num_non_var = len(non_var_len_arg_keys)
4506
4507            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4508            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4509        else:
4510            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4511
4512        return cls(**args_dict)
4513
4514    @classmethod
4515    def sql_names(cls):
4516        if cls is Func:
4517            raise NotImplementedError(
4518                "SQL name is only supported by concrete function implementations"
4519            )
4520        if "_sql_names" not in cls.__dict__:
4521            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4522        return cls._sql_names
4523
4524    @classmethod
4525    def sql_name(cls):
4526        return cls.sql_names()[0]
4527
4528    @classmethod
4529    def default_parser_mappings(cls):
4530        return {name: cls.from_arg_list for name in cls.sql_names()}
4531
4532
4533class AggFunc(Func):
4534    pass
4535
4536
4537class ParameterizedAgg(AggFunc):
4538    arg_types = {"this": True, "expressions": True, "params": True}
4539
4540
4541class Abs(Func):
4542    pass
4543
4544
4545class ArgMax(AggFunc):
4546    arg_types = {"this": True, "expression": True, "count": False}
4547    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4548
4549
4550class ArgMin(AggFunc):
4551    arg_types = {"this": True, "expression": True, "count": False}
4552    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4553
4554
4555class ApproxTopK(AggFunc):
4556    arg_types = {"this": True, "expression": False, "counters": False}
4557
4558
4559class Flatten(Func):
4560    pass
4561
4562
4563# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4564class Transform(Func):
4565    arg_types = {"this": True, "expression": True}
4566
4567
4568class Anonymous(Func):
4569    arg_types = {"this": True, "expressions": False}
4570    is_var_len_args = True
4571
4572    @property
4573    def name(self) -> str:
4574        return self.this if isinstance(self.this, str) else self.this.name
4575
4576
4577class AnonymousAggFunc(AggFunc):
4578    arg_types = {"this": True, "expressions": False}
4579    is_var_len_args = True
4580
4581
4582# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4583class CombinedAggFunc(AnonymousAggFunc):
4584    arg_types = {"this": True, "expressions": False, "parts": True}
4585
4586
4587class CombinedParameterizedAgg(ParameterizedAgg):
4588    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4589
4590
4591# https://docs.snowflake.com/en/sql-reference/functions/hll
4592# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4593class Hll(AggFunc):
4594    arg_types = {"this": True, "expressions": False}
4595    is_var_len_args = True
4596
4597
4598class ApproxDistinct(AggFunc):
4599    arg_types = {"this": True, "accuracy": False}
4600    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4601
4602
4603class Array(Func):
4604    arg_types = {"expressions": False}
4605    is_var_len_args = True
4606
4607
4608# https://docs.snowflake.com/en/sql-reference/functions/to_array
4609class ToArray(Func):
4610    pass
4611
4612
4613# https://docs.snowflake.com/en/sql-reference/functions/to_char
4614# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4615class ToChar(Func):
4616    arg_types = {"this": True, "format": False, "nlsparam": False}
4617
4618
4619# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4620# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4621class ToNumber(Func):
4622    arg_types = {
4623        "this": True,
4624        "format": False,
4625        "nlsparam": False,
4626        "precision": False,
4627        "scale": False,
4628    }
4629
4630
4631# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4632class Convert(Func):
4633    arg_types = {"this": True, "expression": True, "style": False}
4634
4635
4636class GenerateSeries(Func):
4637    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4638
4639
4640class ArrayAgg(AggFunc):
4641    pass
4642
4643
4644class ArrayUniqueAgg(AggFunc):
4645    pass
4646
4647
4648class ArrayAll(Func):
4649    arg_types = {"this": True, "expression": True}
4650
4651
4652# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4653class ArrayAny(Func):
4654    arg_types = {"this": True, "expression": True}
4655
4656
4657class ArrayConcat(Func):
4658    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4659    arg_types = {"this": True, "expressions": False}
4660    is_var_len_args = True
4661
4662
4663class ArrayContains(Binary, Func):
4664    pass
4665
4666
4667class ArrayContained(Binary):
4668    pass
4669
4670
4671class ArrayFilter(Func):
4672    arg_types = {"this": True, "expression": True}
4673    _sql_names = ["FILTER", "ARRAY_FILTER"]
4674
4675
4676class ArrayToString(Func):
4677    arg_types = {"this": True, "expression": True, "null": False}
4678    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4679
4680
4681class ArrayOverlaps(Binary, Func):
4682    pass
4683
4684
4685class ArraySize(Func):
4686    arg_types = {"this": True, "expression": False}
4687    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4688
4689
4690class ArraySort(Func):
4691    arg_types = {"this": True, "expression": False}
4692
4693
4694class ArraySum(Func):
4695    arg_types = {"this": True, "expression": False}
4696
4697
4698class ArrayUnionAgg(AggFunc):
4699    pass
4700
4701
4702class Avg(AggFunc):
4703    pass
4704
4705
4706class AnyValue(AggFunc):
4707    pass
4708
4709
4710class Lag(AggFunc):
4711    arg_types = {"this": True, "offset": False, "default": False}
4712
4713
4714class Lead(AggFunc):
4715    arg_types = {"this": True, "offset": False, "default": False}
4716
4717
4718# some dialects have a distinction between first and first_value, usually first is an aggregate func
4719# and first_value is a window func
4720class First(AggFunc):
4721    pass
4722
4723
4724class Last(AggFunc):
4725    pass
4726
4727
4728class FirstValue(AggFunc):
4729    pass
4730
4731
4732class LastValue(AggFunc):
4733    pass
4734
4735
4736class NthValue(AggFunc):
4737    arg_types = {"this": True, "offset": True}
4738
4739
4740class Case(Func):
4741    arg_types = {"this": False, "ifs": True, "default": False}
4742
4743    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4744        instance = maybe_copy(self, copy)
4745        instance.append(
4746            "ifs",
4747            If(
4748                this=maybe_parse(condition, copy=copy, **opts),
4749                true=maybe_parse(then, copy=copy, **opts),
4750            ),
4751        )
4752        return instance
4753
4754    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4755        instance = maybe_copy(self, copy)
4756        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4757        return instance
4758
4759
4760class Cast(Func):
4761    arg_types = {
4762        "this": True,
4763        "to": True,
4764        "format": False,
4765        "safe": False,
4766        "action": False,
4767    }
4768
4769    @property
4770    def name(self) -> str:
4771        return self.this.name
4772
4773    @property
4774    def to(self) -> DataType:
4775        return self.args["to"]
4776
4777    @property
4778    def output_name(self) -> str:
4779        return self.name
4780
4781    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4782        """
4783        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4784        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4785        array<int> != array<float>.
4786
4787        Args:
4788            dtypes: the data types to compare this Cast's DataType to.
4789
4790        Returns:
4791            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4792        """
4793        return self.to.is_type(*dtypes)
4794
4795
4796class TryCast(Cast):
4797    pass
4798
4799
4800class CastToStrType(Func):
4801    arg_types = {"this": True, "to": True}
4802
4803
4804class Collate(Binary, Func):
4805    pass
4806
4807
4808class Ceil(Func):
4809    arg_types = {"this": True, "decimals": False}
4810    _sql_names = ["CEIL", "CEILING"]
4811
4812
4813class Coalesce(Func):
4814    arg_types = {"this": True, "expressions": False}
4815    is_var_len_args = True
4816    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4817
4818
4819class Chr(Func):
4820    arg_types = {"this": True, "charset": False, "expressions": False}
4821    is_var_len_args = True
4822    _sql_names = ["CHR", "CHAR"]
4823
4824
4825class Concat(Func):
4826    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4827    is_var_len_args = True
4828
4829
4830class ConcatWs(Concat):
4831    _sql_names = ["CONCAT_WS"]
4832
4833
4834# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
4835class ConnectByRoot(Func):
4836    pass
4837
4838
4839class Count(AggFunc):
4840    arg_types = {"this": False, "expressions": False}
4841    is_var_len_args = True
4842
4843
4844class CountIf(AggFunc):
4845    _sql_names = ["COUNT_IF", "COUNTIF"]
4846
4847
4848# cube root
4849class Cbrt(Func):
4850    pass
4851
4852
4853class CurrentDate(Func):
4854    arg_types = {"this": False}
4855
4856
4857class CurrentDatetime(Func):
4858    arg_types = {"this": False}
4859
4860
4861class CurrentTime(Func):
4862    arg_types = {"this": False}
4863
4864
4865class CurrentTimestamp(Func):
4866    arg_types = {"this": False, "transaction": False}
4867
4868
4869class CurrentUser(Func):
4870    arg_types = {"this": False}
4871
4872
4873class DateAdd(Func, IntervalOp):
4874    arg_types = {"this": True, "expression": True, "unit": False}
4875
4876
4877class DateSub(Func, IntervalOp):
4878    arg_types = {"this": True, "expression": True, "unit": False}
4879
4880
4881class DateDiff(Func, TimeUnit):
4882    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4883    arg_types = {"this": True, "expression": True, "unit": False}
4884
4885
4886class DateTrunc(Func):
4887    arg_types = {"unit": True, "this": True, "zone": False}
4888
4889    def __init__(self, **args):
4890        unit = args.get("unit")
4891        if isinstance(unit, TimeUnit.VAR_LIKE):
4892            args["unit"] = Literal.string(
4893                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4894            )
4895        elif isinstance(unit, Week):
4896            unit.set("this", Literal.string(unit.this.name.upper()))
4897
4898        super().__init__(**args)
4899
4900    @property
4901    def unit(self) -> Expression:
4902        return self.args["unit"]
4903
4904
4905class DatetimeAdd(Func, IntervalOp):
4906    arg_types = {"this": True, "expression": True, "unit": False}
4907
4908
4909class DatetimeSub(Func, IntervalOp):
4910    arg_types = {"this": True, "expression": True, "unit": False}
4911
4912
4913class DatetimeDiff(Func, TimeUnit):
4914    arg_types = {"this": True, "expression": True, "unit": False}
4915
4916
4917class DatetimeTrunc(Func, TimeUnit):
4918    arg_types = {"this": True, "unit": True, "zone": False}
4919
4920
4921class DayOfWeek(Func):
4922    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4923
4924
4925class DayOfMonth(Func):
4926    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4927
4928
4929class DayOfYear(Func):
4930    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4931
4932
4933class ToDays(Func):
4934    pass
4935
4936
4937class WeekOfYear(Func):
4938    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4939
4940
4941class MonthsBetween(Func):
4942    arg_types = {"this": True, "expression": True, "roundoff": False}
4943
4944
4945class LastDay(Func, TimeUnit):
4946    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4947    arg_types = {"this": True, "unit": False}
4948
4949
4950class Extract(Func):
4951    arg_types = {"this": True, "expression": True}
4952
4953
4954class Timestamp(Func):
4955    arg_types = {"this": False, "expression": False, "with_tz": False}
4956
4957
4958class TimestampAdd(Func, TimeUnit):
4959    arg_types = {"this": True, "expression": True, "unit": False}
4960
4961
4962class TimestampSub(Func, TimeUnit):
4963    arg_types = {"this": True, "expression": True, "unit": False}
4964
4965
4966class TimestampDiff(Func, TimeUnit):
4967    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4968    arg_types = {"this": True, "expression": True, "unit": False}
4969
4970
4971class TimestampTrunc(Func, TimeUnit):
4972    arg_types = {"this": True, "unit": True, "zone": False}
4973
4974
4975class TimeAdd(Func, TimeUnit):
4976    arg_types = {"this": True, "expression": True, "unit": False}
4977
4978
4979class TimeSub(Func, TimeUnit):
4980    arg_types = {"this": True, "expression": True, "unit": False}
4981
4982
4983class TimeDiff(Func, TimeUnit):
4984    arg_types = {"this": True, "expression": True, "unit": False}
4985
4986
4987class TimeTrunc(Func, TimeUnit):
4988    arg_types = {"this": True, "unit": True, "zone": False}
4989
4990
4991class DateFromParts(Func):
4992    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4993    arg_types = {"year": True, "month": True, "day": True}
4994
4995
4996class TimeFromParts(Func):
4997    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4998    arg_types = {
4999        "hour": True,
5000        "min": True,
5001        "sec": True,
5002        "nano": False,
5003        "fractions": False,
5004        "precision": False,
5005    }
5006
5007
5008class DateStrToDate(Func):
5009    pass
5010
5011
5012class DateToDateStr(Func):
5013    pass
5014
5015
5016class DateToDi(Func):
5017    pass
5018
5019
5020# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5021class Date(Func):
5022    arg_types = {"this": False, "zone": False, "expressions": False}
5023    is_var_len_args = True
5024
5025
5026class Day(Func):
5027    pass
5028
5029
5030class Decode(Func):
5031    arg_types = {"this": True, "charset": True, "replace": False}
5032
5033
5034class DiToDate(Func):
5035    pass
5036
5037
5038class Encode(Func):
5039    arg_types = {"this": True, "charset": True}
5040
5041
5042class Exp(Func):
5043    pass
5044
5045
5046# https://docs.snowflake.com/en/sql-reference/functions/flatten
5047class Explode(Func):
5048    arg_types = {"this": True, "expressions": False}
5049    is_var_len_args = True
5050
5051
5052class ExplodeOuter(Explode):
5053    pass
5054
5055
5056class Posexplode(Explode):
5057    pass
5058
5059
5060class PosexplodeOuter(Posexplode, ExplodeOuter):
5061    pass
5062
5063
5064class Floor(Func):
5065    arg_types = {"this": True, "decimals": False}
5066
5067
5068class FromBase64(Func):
5069    pass
5070
5071
5072class ToBase64(Func):
5073    pass
5074
5075
5076class GenerateDateArray(Func):
5077    arg_types = {"start": True, "end": True, "interval": False}
5078
5079
5080class Greatest(Func):
5081    arg_types = {"this": True, "expressions": False}
5082    is_var_len_args = True
5083
5084
5085class GroupConcat(AggFunc):
5086    arg_types = {"this": True, "separator": False}
5087
5088
5089class Hex(Func):
5090    pass
5091
5092
5093class Xor(Connector, Func):
5094    arg_types = {"this": False, "expression": False, "expressions": False}
5095
5096
5097class If(Func):
5098    arg_types = {"this": True, "true": True, "false": False}
5099    _sql_names = ["IF", "IIF"]
5100
5101
5102class Nullif(Func):
5103    arg_types = {"this": True, "expression": True}
5104
5105
5106class Initcap(Func):
5107    arg_types = {"this": True, "expression": False}
5108
5109
5110class IsNan(Func):
5111    _sql_names = ["IS_NAN", "ISNAN"]
5112
5113
5114class IsInf(Func):
5115    _sql_names = ["IS_INF", "ISINF"]
5116
5117
5118class JSONPath(Expression):
5119    arg_types = {"expressions": True}
5120
5121    @property
5122    def output_name(self) -> str:
5123        last_segment = self.expressions[-1].this
5124        return last_segment if isinstance(last_segment, str) else ""
5125
5126
5127class JSONPathPart(Expression):
5128    arg_types = {}
5129
5130
5131class JSONPathFilter(JSONPathPart):
5132    arg_types = {"this": True}
5133
5134
5135class JSONPathKey(JSONPathPart):
5136    arg_types = {"this": True}
5137
5138
5139class JSONPathRecursive(JSONPathPart):
5140    arg_types = {"this": False}
5141
5142
5143class JSONPathRoot(JSONPathPart):
5144    pass
5145
5146
5147class JSONPathScript(JSONPathPart):
5148    arg_types = {"this": True}
5149
5150
5151class JSONPathSlice(JSONPathPart):
5152    arg_types = {"start": False, "end": False, "step": False}
5153
5154
5155class JSONPathSelector(JSONPathPart):
5156    arg_types = {"this": True}
5157
5158
5159class JSONPathSubscript(JSONPathPart):
5160    arg_types = {"this": True}
5161
5162
5163class JSONPathUnion(JSONPathPart):
5164    arg_types = {"expressions": True}
5165
5166
5167class JSONPathWildcard(JSONPathPart):
5168    pass
5169
5170
5171class FormatJson(Expression):
5172    pass
5173
5174
5175class JSONKeyValue(Expression):
5176    arg_types = {"this": True, "expression": True}
5177
5178
5179class JSONObject(Func):
5180    arg_types = {
5181        "expressions": False,
5182        "null_handling": False,
5183        "unique_keys": False,
5184        "return_type": False,
5185        "encoding": False,
5186    }
5187
5188
5189class JSONObjectAgg(AggFunc):
5190    arg_types = {
5191        "expressions": False,
5192        "null_handling": False,
5193        "unique_keys": False,
5194        "return_type": False,
5195        "encoding": False,
5196    }
5197
5198
5199# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5200class JSONArray(Func):
5201    arg_types = {
5202        "expressions": True,
5203        "null_handling": False,
5204        "return_type": False,
5205        "strict": False,
5206    }
5207
5208
5209# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5210class JSONArrayAgg(Func):
5211    arg_types = {
5212        "this": True,
5213        "order": False,
5214        "null_handling": False,
5215        "return_type": False,
5216        "strict": False,
5217    }
5218
5219
5220# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5221# Note: parsing of JSON column definitions is currently incomplete.
5222class JSONColumnDef(Expression):
5223    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5224
5225
5226class JSONSchema(Expression):
5227    arg_types = {"expressions": True}
5228
5229
5230# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5231class JSONTable(Func):
5232    arg_types = {
5233        "this": True,
5234        "schema": True,
5235        "path": False,
5236        "error_handling": False,
5237        "empty_handling": False,
5238    }
5239
5240
5241class OpenJSONColumnDef(Expression):
5242    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5243
5244
5245class OpenJSON(Func):
5246    arg_types = {"this": True, "path": False, "expressions": False}
5247
5248
5249class JSONBContains(Binary):
5250    _sql_names = ["JSONB_CONTAINS"]
5251
5252
5253class JSONExtract(Binary, Func):
5254    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5255    _sql_names = ["JSON_EXTRACT"]
5256    is_var_len_args = True
5257
5258    @property
5259    def output_name(self) -> str:
5260        return self.expression.output_name if not self.expressions else ""
5261
5262
5263class JSONExtractScalar(Binary, Func):
5264    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5265    _sql_names = ["JSON_EXTRACT_SCALAR"]
5266    is_var_len_args = True
5267
5268    @property
5269    def output_name(self) -> str:
5270        return self.expression.output_name
5271
5272
5273class JSONBExtract(Binary, Func):
5274    _sql_names = ["JSONB_EXTRACT"]
5275
5276
5277class JSONBExtractScalar(Binary, Func):
5278    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5279
5280
5281class JSONFormat(Func):
5282    arg_types = {"this": False, "options": False}
5283    _sql_names = ["JSON_FORMAT"]
5284
5285
5286# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5287class JSONArrayContains(Binary, Predicate, Func):
5288    _sql_names = ["JSON_ARRAY_CONTAINS"]
5289
5290
5291class ParseJSON(Func):
5292    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5293    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5294    arg_types = {"this": True, "expressions": False}
5295    is_var_len_args = True
5296
5297
5298class Least(Func):
5299    arg_types = {"this": True, "expressions": False}
5300    is_var_len_args = True
5301
5302
5303class Left(Func):
5304    arg_types = {"this": True, "expression": True}
5305
5306
5307class Right(Func):
5308    arg_types = {"this": True, "expression": True}
5309
5310
5311class Length(Func):
5312    _sql_names = ["LENGTH", "LEN"]
5313
5314
5315class Levenshtein(Func):
5316    arg_types = {
5317        "this": True,
5318        "expression": False,
5319        "ins_cost": False,
5320        "del_cost": False,
5321        "sub_cost": False,
5322    }
5323
5324
5325class Ln(Func):
5326    pass
5327
5328
5329class Log(Func):
5330    arg_types = {"this": True, "expression": False}
5331
5332
5333class LogicalOr(AggFunc):
5334    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5335
5336
5337class LogicalAnd(AggFunc):
5338    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5339
5340
5341class Lower(Func):
5342    _sql_names = ["LOWER", "LCASE"]
5343
5344
5345class Map(Func):
5346    arg_types = {"keys": False, "values": False}
5347
5348    @property
5349    def keys(self) -> t.List[Expression]:
5350        keys = self.args.get("keys")
5351        return keys.expressions if keys else []
5352
5353    @property
5354    def values(self) -> t.List[Expression]:
5355        values = self.args.get("values")
5356        return values.expressions if values else []
5357
5358
5359class MapFromEntries(Func):
5360    pass
5361
5362
5363class StarMap(Func):
5364    pass
5365
5366
5367class VarMap(Func):
5368    arg_types = {"keys": True, "values": True}
5369    is_var_len_args = True
5370
5371    @property
5372    def keys(self) -> t.List[Expression]:
5373        return self.args["keys"].expressions
5374
5375    @property
5376    def values(self) -> t.List[Expression]:
5377        return self.args["values"].expressions
5378
5379
5380# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5381class MatchAgainst(Func):
5382    arg_types = {"this": True, "expressions": True, "modifier": False}
5383
5384
5385class Max(AggFunc):
5386    arg_types = {"this": True, "expressions": False}
5387    is_var_len_args = True
5388
5389
5390class MD5(Func):
5391    _sql_names = ["MD5"]
5392
5393
5394# Represents the variant of the MD5 function that returns a binary value
5395class MD5Digest(Func):
5396    _sql_names = ["MD5_DIGEST"]
5397
5398
5399class Min(AggFunc):
5400    arg_types = {"this": True, "expressions": False}
5401    is_var_len_args = True
5402
5403
5404class Month(Func):
5405    pass
5406
5407
5408class AddMonths(Func):
5409    arg_types = {"this": True, "expression": True}
5410
5411
5412class Nvl2(Func):
5413    arg_types = {"this": True, "true": True, "false": False}
5414
5415
5416# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5417class Predict(Func):
5418    arg_types = {"this": True, "expression": True, "params_struct": False}
5419
5420
5421class Pow(Binary, Func):
5422    _sql_names = ["POWER", "POW"]
5423
5424
5425class PercentileCont(AggFunc):
5426    arg_types = {"this": True, "expression": False}
5427
5428
5429class PercentileDisc(AggFunc):
5430    arg_types = {"this": True, "expression": False}
5431
5432
5433class Quantile(AggFunc):
5434    arg_types = {"this": True, "quantile": True}
5435
5436
5437class ApproxQuantile(Quantile):
5438    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5439
5440
5441class Rand(Func):
5442    _sql_names = ["RAND", "RANDOM"]
5443    arg_types = {"this": False}
5444
5445
5446class Randn(Func):
5447    arg_types = {"this": False}
5448
5449
5450class RangeN(Func):
5451    arg_types = {"this": True, "expressions": True, "each": False}
5452
5453
5454class ReadCSV(Func):
5455    _sql_names = ["READ_CSV"]
5456    is_var_len_args = True
5457    arg_types = {"this": True, "expressions": False}
5458
5459
5460class Reduce(Func):
5461    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5462
5463
5464class RegexpExtract(Func):
5465    arg_types = {
5466        "this": True,
5467        "expression": True,
5468        "position": False,
5469        "occurrence": False,
5470        "parameters": False,
5471        "group": False,
5472    }
5473
5474
5475class RegexpReplace(Func):
5476    arg_types = {
5477        "this": True,
5478        "expression": True,
5479        "replacement": False,
5480        "position": False,
5481        "occurrence": False,
5482        "parameters": False,
5483        "modifiers": False,
5484    }
5485
5486
5487class RegexpLike(Binary, Func):
5488    arg_types = {"this": True, "expression": True, "flag": False}
5489
5490
5491class RegexpILike(Binary, Func):
5492    arg_types = {"this": True, "expression": True, "flag": False}
5493
5494
5495# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5496# limit is the number of times a pattern is applied
5497class RegexpSplit(Func):
5498    arg_types = {"this": True, "expression": True, "limit": False}
5499
5500
5501class Repeat(Func):
5502    arg_types = {"this": True, "times": True}
5503
5504
5505# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5506# tsql third argument function == trunctaion if not 0
5507class Round(Func):
5508    arg_types = {"this": True, "decimals": False, "truncate": False}
5509
5510
5511class RowNumber(Func):
5512    arg_types: t.Dict[str, t.Any] = {}
5513
5514
5515class SafeDivide(Func):
5516    arg_types = {"this": True, "expression": True}
5517
5518
5519class SHA(Func):
5520    _sql_names = ["SHA", "SHA1"]
5521
5522
5523class SHA2(Func):
5524    _sql_names = ["SHA2"]
5525    arg_types = {"this": True, "length": False}
5526
5527
5528class Sign(Func):
5529    _sql_names = ["SIGN", "SIGNUM"]
5530
5531
5532class SortArray(Func):
5533    arg_types = {"this": True, "asc": False}
5534
5535
5536class Split(Func):
5537    arg_types = {"this": True, "expression": True, "limit": False}
5538
5539
5540# Start may be omitted in the case of postgres
5541# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5542class Substring(Func):
5543    arg_types = {"this": True, "start": False, "length": False}
5544
5545
5546class StandardHash(Func):
5547    arg_types = {"this": True, "expression": False}
5548
5549
5550class StartsWith(Func):
5551    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5552    arg_types = {"this": True, "expression": True}
5553
5554
5555class StrPosition(Func):
5556    arg_types = {
5557        "this": True,
5558        "substr": True,
5559        "position": False,
5560        "instance": False,
5561    }
5562
5563
5564class StrToDate(Func):
5565    arg_types = {"this": True, "format": True}
5566
5567
5568class StrToTime(Func):
5569    arg_types = {"this": True, "format": True, "zone": False}
5570
5571
5572# Spark allows unix_timestamp()
5573# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5574class StrToUnix(Func):
5575    arg_types = {"this": False, "format": False}
5576
5577
5578# https://prestodb.io/docs/current/functions/string.html
5579# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5580class StrToMap(Func):
5581    arg_types = {
5582        "this": True,
5583        "pair_delim": False,
5584        "key_value_delim": False,
5585        "duplicate_resolution_callback": False,
5586    }
5587
5588
5589class NumberToStr(Func):
5590    arg_types = {"this": True, "format": True, "culture": False}
5591
5592
5593class FromBase(Func):
5594    arg_types = {"this": True, "expression": True}
5595
5596
5597class Struct(Func):
5598    arg_types = {"expressions": False}
5599    is_var_len_args = True
5600
5601
5602class StructExtract(Func):
5603    arg_types = {"this": True, "expression": True}
5604
5605
5606# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5607# https://docs.snowflake.com/en/sql-reference/functions/insert
5608class Stuff(Func):
5609    _sql_names = ["STUFF", "INSERT"]
5610    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5611
5612
5613class Sum(AggFunc):
5614    pass
5615
5616
5617class Sqrt(Func):
5618    pass
5619
5620
5621class Stddev(AggFunc):
5622    pass
5623
5624
5625class StddevPop(AggFunc):
5626    pass
5627
5628
5629class StddevSamp(AggFunc):
5630    pass
5631
5632
5633class TimeToStr(Func):
5634    arg_types = {"this": True, "format": True, "culture": False}
5635
5636
5637class TimeToTimeStr(Func):
5638    pass
5639
5640
5641class TimeToUnix(Func):
5642    pass
5643
5644
5645class TimeStrToDate(Func):
5646    pass
5647
5648
5649class TimeStrToTime(Func):
5650    pass
5651
5652
5653class TimeStrToUnix(Func):
5654    pass
5655
5656
5657class Trim(Func):
5658    arg_types = {
5659        "this": True,
5660        "expression": False,
5661        "position": False,
5662        "collation": False,
5663    }
5664
5665
5666class TsOrDsAdd(Func, TimeUnit):
5667    # return_type is used to correctly cast the arguments of this expression when transpiling it
5668    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5669
5670    @property
5671    def return_type(self) -> DataType:
5672        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5673
5674
5675class TsOrDsDiff(Func, TimeUnit):
5676    arg_types = {"this": True, "expression": True, "unit": False}
5677
5678
5679class TsOrDsToDateStr(Func):
5680    pass
5681
5682
5683class TsOrDsToDate(Func):
5684    arg_types = {"this": True, "format": False, "safe": False}
5685
5686
5687class TsOrDsToTime(Func):
5688    pass
5689
5690
5691class TsOrDiToDi(Func):
5692    pass
5693
5694
5695class Unhex(Func):
5696    pass
5697
5698
5699# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5700class UnixDate(Func):
5701    pass
5702
5703
5704class UnixToStr(Func):
5705    arg_types = {"this": True, "format": False}
5706
5707
5708# https://prestodb.io/docs/current/functions/datetime.html
5709# presto has weird zone/hours/minutes
5710class UnixToTime(Func):
5711    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5712
5713    SECONDS = Literal.number(0)
5714    DECIS = Literal.number(1)
5715    CENTIS = Literal.number(2)
5716    MILLIS = Literal.number(3)
5717    DECIMILLIS = Literal.number(4)
5718    CENTIMILLIS = Literal.number(5)
5719    MICROS = Literal.number(6)
5720    DECIMICROS = Literal.number(7)
5721    CENTIMICROS = Literal.number(8)
5722    NANOS = Literal.number(9)
5723
5724
5725class UnixToTimeStr(Func):
5726    pass
5727
5728
5729class TimestampFromParts(Func):
5730    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5731    arg_types = {
5732        "year": True,
5733        "month": True,
5734        "day": True,
5735        "hour": True,
5736        "min": True,
5737        "sec": True,
5738        "nano": False,
5739        "zone": False,
5740        "milli": False,
5741    }
5742
5743
5744class Upper(Func):
5745    _sql_names = ["UPPER", "UCASE"]
5746
5747
5748class Variance(AggFunc):
5749    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5750
5751
5752class VariancePop(AggFunc):
5753    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5754
5755
5756class Week(Func):
5757    arg_types = {"this": True, "mode": False}
5758
5759
5760class XMLTable(Func):
5761    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5762
5763
5764class Year(Func):
5765    pass
5766
5767
5768class Use(Expression):
5769    arg_types = {"this": True, "kind": False}
5770
5771
5772class Merge(Expression):
5773    arg_types = {
5774        "this": True,
5775        "using": True,
5776        "on": True,
5777        "expressions": True,
5778        "with": False,
5779    }
5780
5781
5782class When(Func):
5783    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5784
5785
5786# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5787# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5788class NextValueFor(Func):
5789    arg_types = {"this": True, "order": False}
5790
5791
5792def _norm_arg(arg):
5793    return arg.lower() if type(arg) is str else arg
5794
5795
5796ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5797FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5798
5799JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5800
5801
5802# Helpers
5803@t.overload
5804def maybe_parse(
5805    sql_or_expression: ExpOrStr,
5806    *,
5807    into: t.Type[E],
5808    dialect: DialectType = None,
5809    prefix: t.Optional[str] = None,
5810    copy: bool = False,
5811    **opts,
5812) -> E: ...
5813
5814
5815@t.overload
5816def maybe_parse(
5817    sql_or_expression: str | E,
5818    *,
5819    into: t.Optional[IntoType] = None,
5820    dialect: DialectType = None,
5821    prefix: t.Optional[str] = None,
5822    copy: bool = False,
5823    **opts,
5824) -> E: ...
5825
5826
5827def maybe_parse(
5828    sql_or_expression: ExpOrStr,
5829    *,
5830    into: t.Optional[IntoType] = None,
5831    dialect: DialectType = None,
5832    prefix: t.Optional[str] = None,
5833    copy: bool = False,
5834    **opts,
5835) -> Expression:
5836    """Gracefully handle a possible string or expression.
5837
5838    Example:
5839        >>> maybe_parse("1")
5840        Literal(this=1, is_string=False)
5841        >>> maybe_parse(to_identifier("x"))
5842        Identifier(this=x, quoted=False)
5843
5844    Args:
5845        sql_or_expression: the SQL code string or an expression
5846        into: the SQLGlot Expression to parse into
5847        dialect: the dialect used to parse the input expressions (in the case that an
5848            input expression is a SQL string).
5849        prefix: a string to prefix the sql with before it gets parsed
5850            (automatically includes a space)
5851        copy: whether to copy the expression.
5852        **opts: other options to use to parse the input expressions (again, in the case
5853            that an input expression is a SQL string).
5854
5855    Returns:
5856        Expression: the parsed or given expression.
5857    """
5858    if isinstance(sql_or_expression, Expression):
5859        if copy:
5860            return sql_or_expression.copy()
5861        return sql_or_expression
5862
5863    if sql_or_expression is None:
5864        raise ParseError("SQL cannot be None")
5865
5866    import sqlglot
5867
5868    sql = str(sql_or_expression)
5869    if prefix:
5870        sql = f"{prefix} {sql}"
5871
5872    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5873
5874
5875@t.overload
5876def maybe_copy(instance: None, copy: bool = True) -> None: ...
5877
5878
5879@t.overload
5880def maybe_copy(instance: E, copy: bool = True) -> E: ...
5881
5882
5883def maybe_copy(instance, copy=True):
5884    return instance.copy() if copy and instance else instance
5885
5886
5887def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5888    """Generate a textual representation of an Expression tree"""
5889    indent = "\n" + ("  " * (level + 1))
5890    delim = f",{indent}"
5891
5892    if isinstance(node, Expression):
5893        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5894
5895        if (node.type or verbose) and not isinstance(node, DataType):
5896            args["_type"] = node.type
5897        if node.comments or verbose:
5898            args["_comments"] = node.comments
5899
5900        if verbose:
5901            args["_id"] = id(node)
5902
5903        # Inline leaves for a more compact representation
5904        if node.is_leaf():
5905            indent = ""
5906            delim = ", "
5907
5908        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5909        return f"{node.__class__.__name__}({indent}{items})"
5910
5911    if isinstance(node, list):
5912        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5913        items = f"{indent}{items}" if items else ""
5914        return f"[{items}]"
5915
5916    # Indent multiline strings to match the current level
5917    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5918
5919
5920def _is_wrong_expression(expression, into):
5921    return isinstance(expression, Expression) and not isinstance(expression, into)
5922
5923
5924def _apply_builder(
5925    expression,
5926    instance,
5927    arg,
5928    copy=True,
5929    prefix=None,
5930    into=None,
5931    dialect=None,
5932    into_arg="this",
5933    **opts,
5934):
5935    if _is_wrong_expression(expression, into):
5936        expression = into(**{into_arg: expression})
5937    instance = maybe_copy(instance, copy)
5938    expression = maybe_parse(
5939        sql_or_expression=expression,
5940        prefix=prefix,
5941        into=into,
5942        dialect=dialect,
5943        **opts,
5944    )
5945    instance.set(arg, expression)
5946    return instance
5947
5948
5949def _apply_child_list_builder(
5950    *expressions,
5951    instance,
5952    arg,
5953    append=True,
5954    copy=True,
5955    prefix=None,
5956    into=None,
5957    dialect=None,
5958    properties=None,
5959    **opts,
5960):
5961    instance = maybe_copy(instance, copy)
5962    parsed = []
5963    for expression in expressions:
5964        if expression is not None:
5965            if _is_wrong_expression(expression, into):
5966                expression = into(expressions=[expression])
5967
5968            expression = maybe_parse(
5969                expression,
5970                into=into,
5971                dialect=dialect,
5972                prefix=prefix,
5973                **opts,
5974            )
5975            parsed.extend(expression.expressions)
5976
5977    existing = instance.args.get(arg)
5978    if append and existing:
5979        parsed = existing.expressions + parsed
5980
5981    child = into(expressions=parsed)
5982    for k, v in (properties or {}).items():
5983        child.set(k, v)
5984    instance.set(arg, child)
5985
5986    return instance
5987
5988
5989def _apply_list_builder(
5990    *expressions,
5991    instance,
5992    arg,
5993    append=True,
5994    copy=True,
5995    prefix=None,
5996    into=None,
5997    dialect=None,
5998    **opts,
5999):
6000    inst = maybe_copy(instance, copy)
6001
6002    expressions = [
6003        maybe_parse(
6004            sql_or_expression=expression,
6005            into=into,
6006            prefix=prefix,
6007            dialect=dialect,
6008            **opts,
6009        )
6010        for expression in expressions
6011        if expression is not None
6012    ]
6013
6014    existing_expressions = inst.args.get(arg)
6015    if append and existing_expressions:
6016        expressions = existing_expressions + expressions
6017
6018    inst.set(arg, expressions)
6019    return inst
6020
6021
6022def _apply_conjunction_builder(
6023    *expressions,
6024    instance,
6025    arg,
6026    into=None,
6027    append=True,
6028    copy=True,
6029    dialect=None,
6030    **opts,
6031):
6032    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6033    if not expressions:
6034        return instance
6035
6036    inst = maybe_copy(instance, copy)
6037
6038    existing = inst.args.get(arg)
6039    if append and existing is not None:
6040        expressions = [existing.this if into else existing] + list(expressions)
6041
6042    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6043
6044    inst.set(arg, into(this=node) if into else node)
6045    return inst
6046
6047
6048def _apply_cte_builder(
6049    instance: E,
6050    alias: ExpOrStr,
6051    as_: ExpOrStr,
6052    recursive: t.Optional[bool] = None,
6053    append: bool = True,
6054    dialect: DialectType = None,
6055    copy: bool = True,
6056    **opts,
6057) -> E:
6058    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6059    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6060    cte = CTE(this=as_expression, alias=alias_expression)
6061    return _apply_child_list_builder(
6062        cte,
6063        instance=instance,
6064        arg="with",
6065        append=append,
6066        copy=copy,
6067        into=With,
6068        properties={"recursive": recursive or False},
6069    )
6070
6071
6072def _combine(
6073    expressions: t.Sequence[t.Optional[ExpOrStr]],
6074    operator: t.Type[Connector],
6075    dialect: DialectType = None,
6076    copy: bool = True,
6077    **opts,
6078) -> Expression:
6079    conditions = [
6080        condition(expression, dialect=dialect, copy=copy, **opts)
6081        for expression in expressions
6082        if expression is not None
6083    ]
6084
6085    this, *rest = conditions
6086    if rest:
6087        this = _wrap(this, Connector)
6088    for expression in rest:
6089        this = operator(this=this, expression=_wrap(expression, Connector))
6090
6091    return this
6092
6093
6094def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6095    return Paren(this=expression) if isinstance(expression, kind) else expression
6096
6097
6098def union(
6099    left: ExpOrStr,
6100    right: ExpOrStr,
6101    distinct: bool = True,
6102    dialect: DialectType = None,
6103    copy: bool = True,
6104    **opts,
6105) -> Union:
6106    """
6107    Initializes a syntax tree from one UNION expression.
6108
6109    Example:
6110        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6111        'SELECT * FROM foo UNION SELECT * FROM bla'
6112
6113    Args:
6114        left: the SQL code string corresponding to the left-hand side.
6115            If an `Expression` instance is passed, it will be used as-is.
6116        right: the SQL code string corresponding to the right-hand side.
6117            If an `Expression` instance is passed, it will be used as-is.
6118        distinct: set the DISTINCT flag if and only if this is true.
6119        dialect: the dialect used to parse the input expression.
6120        copy: whether to copy the expression.
6121        opts: other options to use to parse the input expressions.
6122
6123    Returns:
6124        The new Union instance.
6125    """
6126    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6127    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6128
6129    return Union(this=left, expression=right, distinct=distinct)
6130
6131
6132def intersect(
6133    left: ExpOrStr,
6134    right: ExpOrStr,
6135    distinct: bool = True,
6136    dialect: DialectType = None,
6137    copy: bool = True,
6138    **opts,
6139) -> Intersect:
6140    """
6141    Initializes a syntax tree from one INTERSECT expression.
6142
6143    Example:
6144        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6145        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6146
6147    Args:
6148        left: the SQL code string corresponding to the left-hand side.
6149            If an `Expression` instance is passed, it will be used as-is.
6150        right: the SQL code string corresponding to the right-hand side.
6151            If an `Expression` instance is passed, it will be used as-is.
6152        distinct: set the DISTINCT flag if and only if this is true.
6153        dialect: the dialect used to parse the input expression.
6154        copy: whether to copy the expression.
6155        opts: other options to use to parse the input expressions.
6156
6157    Returns:
6158        The new Intersect instance.
6159    """
6160    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6161    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6162
6163    return Intersect(this=left, expression=right, distinct=distinct)
6164
6165
6166def except_(
6167    left: ExpOrStr,
6168    right: ExpOrStr,
6169    distinct: bool = True,
6170    dialect: DialectType = None,
6171    copy: bool = True,
6172    **opts,
6173) -> Except:
6174    """
6175    Initializes a syntax tree from one EXCEPT expression.
6176
6177    Example:
6178        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6179        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6180
6181    Args:
6182        left: the SQL code string corresponding to the left-hand side.
6183            If an `Expression` instance is passed, it will be used as-is.
6184        right: the SQL code string corresponding to the right-hand side.
6185            If an `Expression` instance is passed, it will be used as-is.
6186        distinct: set the DISTINCT flag if and only if this is true.
6187        dialect: the dialect used to parse the input expression.
6188        copy: whether to copy the expression.
6189        opts: other options to use to parse the input expressions.
6190
6191    Returns:
6192        The new Except instance.
6193    """
6194    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6195    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6196
6197    return Except(this=left, expression=right, distinct=distinct)
6198
6199
6200def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6201    """
6202    Initializes a syntax tree from one or multiple SELECT expressions.
6203
6204    Example:
6205        >>> select("col1", "col2").from_("tbl").sql()
6206        'SELECT col1, col2 FROM tbl'
6207
6208    Args:
6209        *expressions: the SQL code string to parse as the expressions of a
6210            SELECT statement. If an Expression instance is passed, this is used as-is.
6211        dialect: the dialect used to parse the input expressions (in the case that an
6212            input expression is a SQL string).
6213        **opts: other options to use to parse the input expressions (again, in the case
6214            that an input expression is a SQL string).
6215
6216    Returns:
6217        Select: the syntax tree for the SELECT statement.
6218    """
6219    return Select().select(*expressions, dialect=dialect, **opts)
6220
6221
6222def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6223    """
6224    Initializes a syntax tree from a FROM expression.
6225
6226    Example:
6227        >>> from_("tbl").select("col1", "col2").sql()
6228        'SELECT col1, col2 FROM tbl'
6229
6230    Args:
6231        *expression: the SQL code string to parse as the FROM expressions of a
6232            SELECT statement. If an Expression instance is passed, this is used as-is.
6233        dialect: the dialect used to parse the input expression (in the case that the
6234            input expression is a SQL string).
6235        **opts: other options to use to parse the input expressions (again, in the case
6236            that the input expression is a SQL string).
6237
6238    Returns:
6239        Select: the syntax tree for the SELECT statement.
6240    """
6241    return Select().from_(expression, dialect=dialect, **opts)
6242
6243
6244def update(
6245    table: str | Table,
6246    properties: dict,
6247    where: t.Optional[ExpOrStr] = None,
6248    from_: t.Optional[ExpOrStr] = None,
6249    dialect: DialectType = None,
6250    **opts,
6251) -> Update:
6252    """
6253    Creates an update statement.
6254
6255    Example:
6256        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6257        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6258
6259    Args:
6260        *properties: dictionary of properties to set which are
6261            auto converted to sql objects eg None -> NULL
6262        where: sql conditional parsed into a WHERE statement
6263        from_: sql statement parsed into a FROM statement
6264        dialect: the dialect used to parse the input expressions.
6265        **opts: other options to use to parse the input expressions.
6266
6267    Returns:
6268        Update: the syntax tree for the UPDATE statement.
6269    """
6270    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6271    update_expr.set(
6272        "expressions",
6273        [
6274            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6275            for k, v in properties.items()
6276        ],
6277    )
6278    if from_:
6279        update_expr.set(
6280            "from",
6281            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6282        )
6283    if isinstance(where, Condition):
6284        where = Where(this=where)
6285    if where:
6286        update_expr.set(
6287            "where",
6288            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6289        )
6290    return update_expr
6291
6292
6293def delete(
6294    table: ExpOrStr,
6295    where: t.Optional[ExpOrStr] = None,
6296    returning: t.Optional[ExpOrStr] = None,
6297    dialect: DialectType = None,
6298    **opts,
6299) -> Delete:
6300    """
6301    Builds a delete statement.
6302
6303    Example:
6304        >>> delete("my_table", where="id > 1").sql()
6305        'DELETE FROM my_table WHERE id > 1'
6306
6307    Args:
6308        where: sql conditional parsed into a WHERE statement
6309        returning: sql conditional parsed into a RETURNING statement
6310        dialect: the dialect used to parse the input expressions.
6311        **opts: other options to use to parse the input expressions.
6312
6313    Returns:
6314        Delete: the syntax tree for the DELETE statement.
6315    """
6316    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6317    if where:
6318        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6319    if returning:
6320        delete_expr = t.cast(
6321            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6322        )
6323    return delete_expr
6324
6325
6326def insert(
6327    expression: ExpOrStr,
6328    into: ExpOrStr,
6329    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6330    overwrite: t.Optional[bool] = None,
6331    returning: t.Optional[ExpOrStr] = None,
6332    dialect: DialectType = None,
6333    copy: bool = True,
6334    **opts,
6335) -> Insert:
6336    """
6337    Builds an INSERT statement.
6338
6339    Example:
6340        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6341        'INSERT INTO tbl VALUES (1, 2, 3)'
6342
6343    Args:
6344        expression: the sql string or expression of the INSERT statement
6345        into: the tbl to insert data to.
6346        columns: optionally the table's column names.
6347        overwrite: whether to INSERT OVERWRITE or not.
6348        returning: sql conditional parsed into a RETURNING statement
6349        dialect: the dialect used to parse the input expressions.
6350        copy: whether to copy the expression.
6351        **opts: other options to use to parse the input expressions.
6352
6353    Returns:
6354        Insert: the syntax tree for the INSERT statement.
6355    """
6356    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6357    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6358
6359    if columns:
6360        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6361
6362    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6363
6364    if returning:
6365        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6366
6367    return insert
6368
6369
6370def condition(
6371    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6372) -> Condition:
6373    """
6374    Initialize a logical condition expression.
6375
6376    Example:
6377        >>> condition("x=1").sql()
6378        'x = 1'
6379
6380        This is helpful for composing larger logical syntax trees:
6381        >>> where = condition("x=1")
6382        >>> where = where.and_("y=1")
6383        >>> Select().from_("tbl").select("*").where(where).sql()
6384        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6385
6386    Args:
6387        *expression: the SQL code string to parse.
6388            If an Expression instance is passed, this is used as-is.
6389        dialect: the dialect used to parse the input expression (in the case that the
6390            input expression is a SQL string).
6391        copy: Whether to copy `expression` (only applies to expressions).
6392        **opts: other options to use to parse the input expressions (again, in the case
6393            that the input expression is a SQL string).
6394
6395    Returns:
6396        The new Condition instance
6397    """
6398    return maybe_parse(
6399        expression,
6400        into=Condition,
6401        dialect=dialect,
6402        copy=copy,
6403        **opts,
6404    )
6405
6406
6407def and_(
6408    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6409) -> Condition:
6410    """
6411    Combine multiple conditions with an AND logical operator.
6412
6413    Example:
6414        >>> and_("x=1", and_("y=1", "z=1")).sql()
6415        'x = 1 AND (y = 1 AND z = 1)'
6416
6417    Args:
6418        *expressions: the SQL code strings to parse.
6419            If an Expression instance is passed, this is used as-is.
6420        dialect: the dialect used to parse the input expression.
6421        copy: whether to copy `expressions` (only applies to Expressions).
6422        **opts: other options to use to parse the input expressions.
6423
6424    Returns:
6425        And: the new condition
6426    """
6427    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6428
6429
6430def or_(
6431    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6432) -> Condition:
6433    """
6434    Combine multiple conditions with an OR logical operator.
6435
6436    Example:
6437        >>> or_("x=1", or_("y=1", "z=1")).sql()
6438        'x = 1 OR (y = 1 OR z = 1)'
6439
6440    Args:
6441        *expressions: the SQL code strings to parse.
6442            If an Expression instance is passed, this is used as-is.
6443        dialect: the dialect used to parse the input expression.
6444        copy: whether to copy `expressions` (only applies to Expressions).
6445        **opts: other options to use to parse the input expressions.
6446
6447    Returns:
6448        Or: the new condition
6449    """
6450    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6451
6452
6453def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6454    """
6455    Wrap a condition with a NOT operator.
6456
6457    Example:
6458        >>> not_("this_suit='black'").sql()
6459        "NOT this_suit = 'black'"
6460
6461    Args:
6462        expression: the SQL code string to parse.
6463            If an Expression instance is passed, this is used as-is.
6464        dialect: the dialect used to parse the input expression.
6465        copy: whether to copy the expression or not.
6466        **opts: other options to use to parse the input expressions.
6467
6468    Returns:
6469        The new condition.
6470    """
6471    this = condition(
6472        expression,
6473        dialect=dialect,
6474        copy=copy,
6475        **opts,
6476    )
6477    return Not(this=_wrap(this, Connector))
6478
6479
6480def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6481    """
6482    Wrap an expression in parentheses.
6483
6484    Example:
6485        >>> paren("5 + 3").sql()
6486        '(5 + 3)'
6487
6488    Args:
6489        expression: the SQL code string to parse.
6490            If an Expression instance is passed, this is used as-is.
6491        copy: whether to copy the expression or not.
6492
6493    Returns:
6494        The wrapped expression.
6495    """
6496    return Paren(this=maybe_parse(expression, copy=copy))
6497
6498
6499SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6500
6501
6502@t.overload
6503def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6504
6505
6506@t.overload
6507def to_identifier(
6508    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6509) -> Identifier: ...
6510
6511
6512def to_identifier(name, quoted=None, copy=True):
6513    """Builds an identifier.
6514
6515    Args:
6516        name: The name to turn into an identifier.
6517        quoted: Whether to force quote the identifier.
6518        copy: Whether to copy name if it's an Identifier.
6519
6520    Returns:
6521        The identifier ast node.
6522    """
6523
6524    if name is None:
6525        return None
6526
6527    if isinstance(name, Identifier):
6528        identifier = maybe_copy(name, copy)
6529    elif isinstance(name, str):
6530        identifier = Identifier(
6531            this=name,
6532            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6533        )
6534    else:
6535        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6536    return identifier
6537
6538
6539def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6540    """
6541    Parses a given string into an identifier.
6542
6543    Args:
6544        name: The name to parse into an identifier.
6545        dialect: The dialect to parse against.
6546
6547    Returns:
6548        The identifier ast node.
6549    """
6550    try:
6551        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6552    except ParseError:
6553        expression = to_identifier(name)
6554
6555    return expression
6556
6557
6558INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6559
6560
6561def to_interval(interval: str | Literal) -> Interval:
6562    """Builds an interval expression from a string like '1 day' or '5 months'."""
6563    if isinstance(interval, Literal):
6564        if not interval.is_string:
6565            raise ValueError("Invalid interval string.")
6566
6567        interval = interval.this
6568
6569    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6570
6571    if not interval_parts:
6572        raise ValueError("Invalid interval string.")
6573
6574    return Interval(
6575        this=Literal.string(interval_parts.group(1)),
6576        unit=Var(this=interval_parts.group(2).upper()),
6577    )
6578
6579
6580@t.overload
6581def to_table(sql_path: str | Table, **kwargs) -> Table: ...
6582
6583
6584@t.overload
6585def to_table(sql_path: None, **kwargs) -> None: ...
6586
6587
6588def to_table(
6589    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6590) -> t.Optional[Table]:
6591    """
6592    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6593    If a table is passed in then that table is returned.
6594
6595    Args:
6596        sql_path: a `[catalog].[schema].[table]` string.
6597        dialect: the source dialect according to which the table name will be parsed.
6598        copy: Whether to copy a table if it is passed in.
6599        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6600
6601    Returns:
6602        A table expression.
6603    """
6604    if sql_path is None or isinstance(sql_path, Table):
6605        return maybe_copy(sql_path, copy=copy)
6606    if not isinstance(sql_path, str):
6607        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6608
6609    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6610    if table:
6611        for k, v in kwargs.items():
6612            table.set(k, v)
6613
6614    return table
6615
6616
6617def to_column(sql_path: str | Column, **kwargs) -> Column:
6618    """
6619    Create a column from a `[table].[column]` sql path. Schema is optional.
6620
6621    If a column is passed in then that column is returned.
6622
6623    Args:
6624        sql_path: `[table].[column]` string
6625    Returns:
6626        Table: A column expression
6627    """
6628    if sql_path is None or isinstance(sql_path, Column):
6629        return sql_path
6630    if not isinstance(sql_path, str):
6631        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6632    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6633
6634
6635def alias_(
6636    expression: ExpOrStr,
6637    alias: t.Optional[str | Identifier],
6638    table: bool | t.Sequence[str | Identifier] = False,
6639    quoted: t.Optional[bool] = None,
6640    dialect: DialectType = None,
6641    copy: bool = True,
6642    **opts,
6643):
6644    """Create an Alias expression.
6645
6646    Example:
6647        >>> alias_('foo', 'bar').sql()
6648        'foo AS bar'
6649
6650        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6651        '(SELECT 1, 2) AS bar(a, b)'
6652
6653    Args:
6654        expression: the SQL code strings to parse.
6655            If an Expression instance is passed, this is used as-is.
6656        alias: the alias name to use. If the name has
6657            special characters it is quoted.
6658        table: Whether to create a table alias, can also be a list of columns.
6659        quoted: whether to quote the alias
6660        dialect: the dialect used to parse the input expression.
6661        copy: Whether to copy the expression.
6662        **opts: other options to use to parse the input expressions.
6663
6664    Returns:
6665        Alias: the aliased expression
6666    """
6667    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6668    alias = to_identifier(alias, quoted=quoted)
6669
6670    if table:
6671        table_alias = TableAlias(this=alias)
6672        exp.set("alias", table_alias)
6673
6674        if not isinstance(table, bool):
6675            for column in table:
6676                table_alias.append("columns", to_identifier(column, quoted=quoted))
6677
6678        return exp
6679
6680    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6681    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6682    # for the complete Window expression.
6683    #
6684    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6685
6686    if "alias" in exp.arg_types and not isinstance(exp, Window):
6687        exp.set("alias", alias)
6688        return exp
6689    return Alias(this=exp, alias=alias)
6690
6691
6692def subquery(
6693    expression: ExpOrStr,
6694    alias: t.Optional[Identifier | str] = None,
6695    dialect: DialectType = None,
6696    **opts,
6697) -> Select:
6698    """
6699    Build a subquery expression that's selected from.
6700
6701    Example:
6702        >>> subquery('select x from tbl', 'bar').select('x').sql()
6703        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6704
6705    Args:
6706        expression: the SQL code strings to parse.
6707            If an Expression instance is passed, this is used as-is.
6708        alias: the alias name to use.
6709        dialect: the dialect used to parse the input expression.
6710        **opts: other options to use to parse the input expressions.
6711
6712    Returns:
6713        A new Select instance with the subquery expression included.
6714    """
6715
6716    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6717    return Select().from_(expression, dialect=dialect, **opts)
6718
6719
6720@t.overload
6721def column(
6722    col: str | Identifier,
6723    table: t.Optional[str | Identifier] = None,
6724    db: t.Optional[str | Identifier] = None,
6725    catalog: t.Optional[str | Identifier] = None,
6726    *,
6727    fields: t.Collection[t.Union[str, Identifier]],
6728    quoted: t.Optional[bool] = None,
6729    copy: bool = True,
6730) -> Dot:
6731    pass
6732
6733
6734@t.overload
6735def column(
6736    col: str | Identifier,
6737    table: t.Optional[str | Identifier] = None,
6738    db: t.Optional[str | Identifier] = None,
6739    catalog: t.Optional[str | Identifier] = None,
6740    *,
6741    fields: Lit[None] = None,
6742    quoted: t.Optional[bool] = None,
6743    copy: bool = True,
6744) -> Column:
6745    pass
6746
6747
6748def column(
6749    col,
6750    table=None,
6751    db=None,
6752    catalog=None,
6753    *,
6754    fields=None,
6755    quoted=None,
6756    copy=True,
6757):
6758    """
6759    Build a Column.
6760
6761    Args:
6762        col: Column name.
6763        table: Table name.
6764        db: Database name.
6765        catalog: Catalog name.
6766        fields: Additional fields using dots.
6767        quoted: Whether to force quotes on the column's identifiers.
6768        copy: Whether to copy identifiers if passed in.
6769
6770    Returns:
6771        The new Column instance.
6772    """
6773    this = Column(
6774        this=to_identifier(col, quoted=quoted, copy=copy),
6775        table=to_identifier(table, quoted=quoted, copy=copy),
6776        db=to_identifier(db, quoted=quoted, copy=copy),
6777        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6778    )
6779
6780    if fields:
6781        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6782    return this
6783
6784
6785def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6786    """Cast an expression to a data type.
6787
6788    Example:
6789        >>> cast('x + 1', 'int').sql()
6790        'CAST(x + 1 AS INT)'
6791
6792    Args:
6793        expression: The expression to cast.
6794        to: The datatype to cast to.
6795        copy: Whether to copy the supplied expressions.
6796
6797    Returns:
6798        The new Cast instance.
6799    """
6800    expression = maybe_parse(expression, copy=copy, **opts)
6801    data_type = DataType.build(to, copy=copy, **opts)
6802    expression = Cast(this=expression, to=data_type)
6803    expression.type = data_type
6804    return expression
6805
6806
6807def table_(
6808    table: Identifier | str,
6809    db: t.Optional[Identifier | str] = None,
6810    catalog: t.Optional[Identifier | str] = None,
6811    quoted: t.Optional[bool] = None,
6812    alias: t.Optional[Identifier | str] = None,
6813) -> Table:
6814    """Build a Table.
6815
6816    Args:
6817        table: Table name.
6818        db: Database name.
6819        catalog: Catalog name.
6820        quote: Whether to force quotes on the table's identifiers.
6821        alias: Table's alias.
6822
6823    Returns:
6824        The new Table instance.
6825    """
6826    return Table(
6827        this=to_identifier(table, quoted=quoted) if table else None,
6828        db=to_identifier(db, quoted=quoted) if db else None,
6829        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6830        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6831    )
6832
6833
6834def values(
6835    values: t.Iterable[t.Tuple[t.Any, ...]],
6836    alias: t.Optional[str] = None,
6837    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6838) -> Values:
6839    """Build VALUES statement.
6840
6841    Example:
6842        >>> values([(1, '2')]).sql()
6843        "VALUES (1, '2')"
6844
6845    Args:
6846        values: values statements that will be converted to SQL
6847        alias: optional alias
6848        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6849         If either are provided then an alias is also required.
6850
6851    Returns:
6852        Values: the Values expression object
6853    """
6854    if columns and not alias:
6855        raise ValueError("Alias is required when providing columns")
6856
6857    return Values(
6858        expressions=[convert(tup) for tup in values],
6859        alias=(
6860            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6861            if columns
6862            else (TableAlias(this=to_identifier(alias)) if alias else None)
6863        ),
6864    )
6865
6866
6867def var(name: t.Optional[ExpOrStr]) -> Var:
6868    """Build a SQL variable.
6869
6870    Example:
6871        >>> repr(var('x'))
6872        'Var(this=x)'
6873
6874        >>> repr(var(column('x', table='y')))
6875        'Var(this=x)'
6876
6877    Args:
6878        name: The name of the var or an expression who's name will become the var.
6879
6880    Returns:
6881        The new variable node.
6882    """
6883    if not name:
6884        raise ValueError("Cannot convert empty name into var.")
6885
6886    if isinstance(name, Expression):
6887        name = name.name
6888    return Var(this=name)
6889
6890
6891def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6892    """Build ALTER TABLE... RENAME... expression
6893
6894    Args:
6895        old_name: The old name of the table
6896        new_name: The new name of the table
6897
6898    Returns:
6899        Alter table expression
6900    """
6901    old_table = to_table(old_name)
6902    new_table = to_table(new_name)
6903    return AlterTable(
6904        this=old_table,
6905        actions=[
6906            RenameTable(this=new_table),
6907        ],
6908    )
6909
6910
6911def rename_column(
6912    table_name: str | Table,
6913    old_column_name: str | Column,
6914    new_column_name: str | Column,
6915    exists: t.Optional[bool] = None,
6916) -> AlterTable:
6917    """Build ALTER TABLE... RENAME COLUMN... expression
6918
6919    Args:
6920        table_name: Name of the table
6921        old_column: The old name of the column
6922        new_column: The new name of the column
6923        exists: Whether to add the `IF EXISTS` clause
6924
6925    Returns:
6926        Alter table expression
6927    """
6928    table = to_table(table_name)
6929    old_column = to_column(old_column_name)
6930    new_column = to_column(new_column_name)
6931    return AlterTable(
6932        this=table,
6933        actions=[
6934            RenameColumn(this=old_column, to=new_column, exists=exists),
6935        ],
6936    )
6937
6938
6939def convert(value: t.Any, copy: bool = False) -> Expression:
6940    """Convert a python value into an expression object.
6941
6942    Raises an error if a conversion is not possible.
6943
6944    Args:
6945        value: A python object.
6946        copy: Whether to copy `value` (only applies to Expressions and collections).
6947
6948    Returns:
6949        Expression: the equivalent expression object.
6950    """
6951    if isinstance(value, Expression):
6952        return maybe_copy(value, copy)
6953    if isinstance(value, str):
6954        return Literal.string(value)
6955    if isinstance(value, bool):
6956        return Boolean(this=value)
6957    if value is None or (isinstance(value, float) and math.isnan(value)):
6958        return null()
6959    if isinstance(value, numbers.Number):
6960        return Literal.number(value)
6961    if isinstance(value, datetime.datetime):
6962        datetime_literal = Literal.string(
6963            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6964        )
6965        return TimeStrToTime(this=datetime_literal)
6966    if isinstance(value, datetime.date):
6967        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6968        return DateStrToDate(this=date_literal)
6969    if isinstance(value, tuple):
6970        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6971    if isinstance(value, list):
6972        return Array(expressions=[convert(v, copy=copy) for v in value])
6973    if isinstance(value, dict):
6974        return Map(
6975            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6976            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6977        )
6978    raise ValueError(f"Cannot convert {value}")
6979
6980
6981def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6982    """
6983    Replace children of an expression with the result of a lambda fun(child) -> exp.
6984    """
6985    for k, v in tuple(expression.args.items()):
6986        is_list_arg = type(v) is list
6987
6988        child_nodes = v if is_list_arg else [v]
6989        new_child_nodes = []
6990
6991        for cn in child_nodes:
6992            if isinstance(cn, Expression):
6993                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6994                    new_child_nodes.append(child_node)
6995            else:
6996                new_child_nodes.append(cn)
6997
6998        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
6999
7000
7001def replace_tree(
7002    expression: Expression,
7003    fun: t.Callable,
7004    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7005) -> Expression:
7006    """
7007    Replace an entire tree with the result of function calls on each node.
7008
7009    This will be traversed in reverse dfs, so leaves first.
7010    If new nodes are created as a result of function calls, they will also be traversed.
7011    """
7012    stack = list(expression.dfs(prune=prune))
7013
7014    while stack:
7015        node = stack.pop()
7016        new_node = fun(node)
7017
7018        if new_node is not node:
7019            node.replace(new_node)
7020
7021            if isinstance(new_node, Expression):
7022                stack.append(new_node)
7023
7024    return new_node
7025
7026
7027def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7028    """
7029    Return all table names referenced through columns in an expression.
7030
7031    Example:
7032        >>> import sqlglot
7033        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7034        ['a', 'c']
7035
7036    Args:
7037        expression: expression to find table names.
7038        exclude: a table name to exclude
7039
7040    Returns:
7041        A list of unique names.
7042    """
7043    return {
7044        table
7045        for table in (column.table for column in expression.find_all(Column))
7046        if table and table != exclude
7047    }
7048
7049
7050def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7051    """Get the full name of a table as a string.
7052
7053    Args:
7054        table: Table expression node or string.
7055        dialect: The dialect to generate the table name for.
7056        identify: Determines when an identifier should be quoted. Possible values are:
7057            False (default): Never quote, except in cases where it's mandatory by the dialect.
7058            True: Always quote.
7059
7060    Examples:
7061        >>> from sqlglot import exp, parse_one
7062        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7063        'a.b.c'
7064
7065    Returns:
7066        The table name.
7067    """
7068
7069    table = maybe_parse(table, into=Table, dialect=dialect)
7070
7071    if not table:
7072        raise ValueError(f"Cannot parse {table}")
7073
7074    return ".".join(
7075        (
7076            part.sql(dialect=dialect, identify=True, copy=False)
7077            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7078            else part.name
7079        )
7080        for part in table.parts
7081    )
7082
7083
7084def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7085    """Returns a case normalized table name without quotes.
7086
7087    Args:
7088        table: the table to normalize
7089        dialect: the dialect to use for normalization rules
7090        copy: whether to copy the expression.
7091
7092    Examples:
7093        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7094        'A-B.c'
7095    """
7096    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7097
7098    return ".".join(
7099        p.name
7100        for p in normalize_identifiers(
7101            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7102        ).parts
7103    )
7104
7105
7106def replace_tables(
7107    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7108) -> E:
7109    """Replace all tables in expression according to the mapping.
7110
7111    Args:
7112        expression: expression node to be transformed and replaced.
7113        mapping: mapping of table names.
7114        dialect: the dialect of the mapping table
7115        copy: whether to copy the expression.
7116
7117    Examples:
7118        >>> from sqlglot import exp, parse_one
7119        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7120        'SELECT * FROM c /* a.b */'
7121
7122    Returns:
7123        The mapped expression.
7124    """
7125
7126    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7127
7128    def _replace_tables(node: Expression) -> Expression:
7129        if isinstance(node, Table):
7130            original = normalize_table_name(node, dialect=dialect)
7131            new_name = mapping.get(original)
7132
7133            if new_name:
7134                table = to_table(
7135                    new_name,
7136                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7137                    dialect=dialect,
7138                )
7139                table.add_comments([original])
7140                return table
7141        return node
7142
7143    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7144
7145
7146def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7147    """Replace placeholders in an expression.
7148
7149    Args:
7150        expression: expression node to be transformed and replaced.
7151        args: positional names that will substitute unnamed placeholders in the given order.
7152        kwargs: keyword arguments that will substitute named placeholders.
7153
7154    Examples:
7155        >>> from sqlglot import exp, parse_one
7156        >>> replace_placeholders(
7157        ...     parse_one("select * from :tbl where ? = ?"),
7158        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7159        ... ).sql()
7160        "SELECT * FROM foo WHERE str_col = 'b'"
7161
7162    Returns:
7163        The mapped expression.
7164    """
7165
7166    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7167        if isinstance(node, Placeholder):
7168            if node.name:
7169                new_name = kwargs.get(node.name)
7170                if new_name is not None:
7171                    return convert(new_name)
7172            else:
7173                try:
7174                    return convert(next(args))
7175                except StopIteration:
7176                    pass
7177        return node
7178
7179    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7180
7181
7182def expand(
7183    expression: Expression,
7184    sources: t.Dict[str, Query],
7185    dialect: DialectType = None,
7186    copy: bool = True,
7187) -> Expression:
7188    """Transforms an expression by expanding all referenced sources into subqueries.
7189
7190    Examples:
7191        >>> from sqlglot import parse_one
7192        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7193        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7194
7195        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7196        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7197
7198    Args:
7199        expression: The expression to expand.
7200        sources: A dictionary of name to Queries.
7201        dialect: The dialect of the sources dict.
7202        copy: Whether to copy the expression during transformation. Defaults to True.
7203
7204    Returns:
7205        The transformed expression.
7206    """
7207    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7208
7209    def _expand(node: Expression):
7210        if isinstance(node, Table):
7211            name = normalize_table_name(node, dialect=dialect)
7212            source = sources.get(name)
7213            if source:
7214                subquery = source.subquery(node.alias or name)
7215                subquery.comments = [f"source: {name}"]
7216                return subquery.transform(_expand, copy=False)
7217        return node
7218
7219    return expression.transform(_expand, copy=copy)
7220
7221
7222def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7223    """
7224    Returns a Func expression.
7225
7226    Examples:
7227        >>> func("abs", 5).sql()
7228        'ABS(5)'
7229
7230        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7231        'CAST(5 AS DOUBLE)'
7232
7233    Args:
7234        name: the name of the function to build.
7235        args: the args used to instantiate the function of interest.
7236        copy: whether to copy the argument expressions.
7237        dialect: the source dialect.
7238        kwargs: the kwargs used to instantiate the function of interest.
7239
7240    Note:
7241        The arguments `args` and `kwargs` are mutually exclusive.
7242
7243    Returns:
7244        An instance of the function of interest, or an anonymous function, if `name` doesn't
7245        correspond to an existing `sqlglot.expressions.Func` class.
7246    """
7247    if args and kwargs:
7248        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7249
7250    from sqlglot.dialects.dialect import Dialect
7251
7252    dialect = Dialect.get_or_raise(dialect)
7253
7254    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7255    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7256
7257    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7258    if constructor:
7259        if converted:
7260            if "dialect" in constructor.__code__.co_varnames:
7261                function = constructor(converted, dialect=dialect)
7262            else:
7263                function = constructor(converted)
7264        elif constructor.__name__ == "from_arg_list":
7265            function = constructor.__self__(**kwargs)  # type: ignore
7266        else:
7267            constructor = FUNCTION_BY_NAME.get(name.upper())
7268            if constructor:
7269                function = constructor(**kwargs)
7270            else:
7271                raise ValueError(
7272                    f"Unable to convert '{name}' into a Func. Either manually construct "
7273                    "the Func expression of interest or parse the function call."
7274                )
7275    else:
7276        kwargs = kwargs or {"expressions": converted}
7277        function = Anonymous(this=name, **kwargs)
7278
7279    for error_message in function.error_messages(converted):
7280        raise ValueError(error_message)
7281
7282    return function
7283
7284
7285def case(
7286    expression: t.Optional[ExpOrStr] = None,
7287    **opts,
7288) -> Case:
7289    """
7290    Initialize a CASE statement.
7291
7292    Example:
7293        case().when("a = 1", "foo").else_("bar")
7294
7295    Args:
7296        expression: Optionally, the input expression (not all dialects support this)
7297        **opts: Extra keyword arguments for parsing `expression`
7298    """
7299    if expression is not None:
7300        this = maybe_parse(expression, **opts)
7301    else:
7302        this = None
7303    return Case(this=this, ifs=[])
7304
7305
7306def cast_unless(
7307    expression: ExpOrStr,
7308    to: DATA_TYPE,
7309    *types: DATA_TYPE,
7310    **opts: t.Any,
7311) -> Expression | Cast:
7312    """
7313    Cast an expression to a data type unless it is a specified type.
7314
7315    Args:
7316        expression: The expression to cast.
7317        to: The data type to cast to.
7318        **types: The types to exclude from casting.
7319        **opts: Extra keyword arguments for parsing `expression`
7320    """
7321    expr = maybe_parse(expression, **opts)
7322    if expr.is_type(*types):
7323        return expr
7324    return cast(expr, to, **opts)
7325
7326
7327def array(
7328    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7329) -> Array:
7330    """
7331    Returns an array.
7332
7333    Examples:
7334        >>> array(1, 'x').sql()
7335        'ARRAY(1, x)'
7336
7337    Args:
7338        expressions: the expressions to add to the array.
7339        copy: whether to copy the argument expressions.
7340        dialect: the source dialect.
7341        kwargs: the kwargs used to instantiate the function of interest.
7342
7343    Returns:
7344        An array expression.
7345    """
7346    return Array(
7347        expressions=[
7348            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7349            for expression in expressions
7350        ]
7351    )
7352
7353
7354def tuple_(
7355    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7356) -> Tuple:
7357    """
7358    Returns an tuple.
7359
7360    Examples:
7361        >>> tuple_(1, 'x').sql()
7362        '(1, x)'
7363
7364    Args:
7365        expressions: the expressions to add to the tuple.
7366        copy: whether to copy the argument expressions.
7367        dialect: the source dialect.
7368        kwargs: the kwargs used to instantiate the function of interest.
7369
7370    Returns:
7371        A tuple expression.
7372    """
7373    return Tuple(
7374        expressions=[
7375            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7376            for expression in expressions
7377        ]
7378    )
7379
7380
7381def true() -> Boolean:
7382    """
7383    Returns a true Boolean expression.
7384    """
7385    return Boolean(this=True)
7386
7387
7388def false() -> Boolean:
7389    """
7390    Returns a false Boolean expression.
7391    """
7392    return Boolean(this=False)
7393
7394
7395def null() -> Null:
7396    """
7397    Returns a Null expression.
7398    """
7399    return Null()
7400
7401
7402NONNULL_CONSTANTS = (
7403    Literal,
7404    Boolean,
7405)
7406
7407CONSTANTS = (
7408    Literal,
7409    Boolean,
7410    Null,
7411)